|
|
|
kak sostavit' SQL zapros ...
|
|||
|---|---|---|---|
|
#18+
Est' tablichka s 2mja poljami Customer| Project ----------------- User1 | Project1 User1 | Project2 User2 | Project3 User3 | Project1 User3 | Project2 User3 | Project3 kak vybrat' vseh Customers kotorye imejut Project1 AND Project2 ? Thanx, Far ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 12.10.2000, 23:51 |
|
||
|
kak sostavit' SQL zapros ...
|
|||
|---|---|---|---|
|
#18+
create table #tbl( Customer varchar(30), Project varchar(30)) insert #tbl select 'User1','Project1' insert #tbl select 'User1','Project2' insert #tbl select 'User2','Project3' insert #tbl select 'User3','Project1' insert #tbl select 'User3','Project2' insert #tbl select 'User3','Project3' select distinct t1.Customer from #tbl t1,#tbl t2 where t1.Customer=t2.Customer and t1.Project='Project1' and t2.Project='Project2' Годиться? ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 13.10.2000, 05:28 |
|
||
|
kak sostavit' SQL zapros ...
|
|||
|---|---|---|---|
|
#18+
select customer from ( select customer from Table1 where project = 'project1' or project = 'project2') tmp group by customer having count(customer) !< 2 --На случай повторяющихся записей. Если повроряющихся нет, то можно =2 или вложенном запросе написать distinct . ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 13.10.2000, 05:35 |
|
||
|
kak sostavit' SQL zapros ...
|
|||
|---|---|---|---|
|
#18+
1. select distinct Customer from Table1 t1, Table1 t2 where t1.Customer=t2.Customer and t1.Project='Project1' and t2.Project='Project2' 2. select Customer from Table1 t1 where Project='Project1' and exists(select * from Table1 t2 where t2.Project='Project2' and t1.Customer=t2.Customer) ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 13.10.2000, 05:59 |
|
||
|
kak sostavit' SQL zapros ...
|
|||
|---|---|---|---|
|
#18+
Spasibo bol'shushee, problema nemnogo projasnilas', NO est' nekotorye detali .. Delo v tom chto chislo Projectov kotorye dolzhy vhodit' v zapros var'jiruet, eto mozhet byt' zapros najti Customers kotorye imejut 1, 2 .. ili 100 konkretnyh proektov, sama tablichka Customer_Project ochen' bol'shaja, sozdovat' stol'ko kopii tablichki Customer_Project, skol'ko Project's v zaprose - nakladno, da i zapros poluchitsja ogromnyj s ogromnum kolichestvom vlozhennyh podzaprosov. Net li bolee universal'nogo reshenija etoj problemy, tipa Select Customers FROM Customer_Project WHERE Project IN("Project1", "Project2", ... "ProjectN") no podsatvit' mezdu imenami proektov AND ? ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 13.10.2000, 17:27 |
|
||
|
kak sostavit' SQL zapros ...
|
|||
|---|---|---|---|
|
#18+
Ну барин, ты и задачки ставишь :) create table #tbl( Customer varchar(30), Project varchar(30)) insert #tbl select 'User1','Project1' insert #tbl select 'User1','Project2' insert #tbl select 'User2','Project3' insert #tbl select 'User3','Project1' insert #tbl select 'User3','Project2' insert #tbl select 'User3','Project3' --#s - это таблица с нужными проектами create table #s(Project varchar(30)) insert #s select 'Project1' insert #s select 'Project2' declare @num_project int select @num_project=count(*) from #s select Customer from #tbl t, #s s where t.Project=s.Project group by Customer having count(*)=@num_project Смысл, надеюсь, понятен. С приветом Сергей sergsuper@mail.ru ... |
|||
|
:
Нравится:
Не нравится:
|
|||
| 16.10.2000, 05:30 |
|
||
|
|
start [/forum/topic.php?fid=46&msg=32000461&tid=1827672]: |
0ms |
get settings: |
10ms |
get forum list: |
14ms |
check forum access: |
5ms |
check topic access: |
5ms |
track hit: |
181ms |
get topic data: |
11ms |
get forum data: |
3ms |
get page messages: |
50ms |
get tp. blocked users: |
2ms |
| others: | 13ms |
| total: | 294ms |
| 0 / 0 |
