CREATE TABLE #WAN( [NAME] VARCHAR(10), [TIME] VARCHAR(10)) INSERT INTO #WAN VALUES('A','2003') INSERT INTO #WAN VALUES('B','2003') INSERT INTO #WAN VALUES('C','2003') INSERT INTO #WAN VALUES('A','2004') INSERT INTO #WAN VALUES('C','2004') INSERT INTO #WAN VALUES('B','2005') INSERT INTO #WAN VALUES('D','2005') INSERT INTO #WAN VALUES('A','2005') INSERT INTO #WAN VALUES('B','2004') SELECT A.* FROM #WAN A,( SELECT [NAME] FROM (SELECT * FROM #WAN GROUP BY [NAME],[TIME]) T GROUP BY [NAME] HAVING COUNT([TIME])=(SELECT COUNT(*) FROM (SELECT [TIME] FROM #WAN GROUP BY [TIME]) [TIME]) ) B WHERE A.[NAME]=B.[NAME] ORDER BY A.[TIME],A.[NAME]DROP TABLE #WAN/*
显示结果: NAME TIME A 2003 B 2003 A 2004 B 2004 A 2005 B 2005 */
select * from tab where name in( select a.name from (select name , count(*) times from tab group by name) a, (select count(distinct time) times from tab) b where a.times = b.times) order by time,name
--换数据类型也是可以的,我现在把time换成int CREATE TABLE #WAN( [NAME] VARCHAR(10), [TIME] int) INSERT INTO #WAN VALUES('A',2003) INSERT INTO #WAN VALUES('B',2003) INSERT INTO #WAN VALUES('C',2003) INSERT INTO #WAN VALUES('A',2004) INSERT INTO #WAN VALUES('C',2004) INSERT INTO #WAN VALUES('B',2005) INSERT INTO #WAN VALUES('D',2005) INSERT INTO #WAN VALUES('A',2005) INSERT INTO #WAN VALUES('B',2004) SELECT A.* FROM #WAN A,( SELECT [NAME] FROM (SELECT * FROM #WAN GROUP BY [NAME],[TIME]) T GROUP BY [NAME] HAVING COUNT([TIME])=(SELECT COUNT(*) FROM (SELECT [TIME] FROM #WAN GROUP BY [TIME]) [TIME]) ) B WHERE A.[NAME]=B.[NAME] ORDER BY A.[TIME],A.[NAME]DROP TABLE #WAN/*
显示结果: NAME TIME A 2003 B 2003 A 2004 B 2004 A 2005 B 2005 */
select a.* from tb a,( select name from tb a group by name having count(*)=(select count(distinct time) from tb) )b where a.name=b.name
--测试--测试数据 create table tb(name varchar(10),time int) insert tb select 'A',2003 union all select 'B',2003 union all select 'C',2003 union all select 'A',2004 union all select 'C',2004 union all select 'B',2005 union all select 'D',2005 union all select 'A',2005 union all select 'B',2004 goselect a.* from tb a,( select name from tb a group by name having count(*)=(select count(distinct time) from tb) )b where a.name=b.name order by a.time,a.name go--删除测试 drop table tb/*--测试结果name time ---------- ----------- A 2003 B 2003 A 2004 B 2004 A 2005 B 2005(所影响的行数为 6 行) --*/
table1--你的第一个表 table2--你的第二个表 select distinct(name) from table1 a,table2 b where a.name=b.name这样可以吗?
这样就只显示出两个表的name的交集:A B 了
CREATE TABLE #EDW( [NAME] VARCHAR(10), [TIME] int) INSERT INTO #EDW VALUES('A',2003) INSERT INTO #EDW VALUES('B',2003) INSERT INTO #EDW VALUES('C',2003) INSERT INTO #EDW VALUES('A',2004) INSERT INTO #EDW VALUES('C',2004) INSERT INTO #EDW VALUES('B',2005) INSERT INTO #EDW VALUES('D',2005) INSERT INTO #EDW VALUES('A',2005) INSERT INTO #EDW VALUES('B',2004)select a.* from #EDW a inner join (select name,sum(time) as tt from #EDW group by name having sum(time) = 6012) b on a.name = b.name order by a.time
NAME TIME ---------- ----------- A 2003 B 2003 A 2004 B 2004 B 2005 A 2005(6 row(s) affected)嘿嘿 把time字段改成整数型了(根据楼主情况)
from tb
group by name , time不会是这个意思吧
CREATE TABLE #WAN(
[NAME] VARCHAR(10),
[TIME] VARCHAR(10))
INSERT INTO #WAN VALUES('A','2003')
INSERT INTO #WAN VALUES('B','2003')
INSERT INTO #WAN VALUES('C','2003')
INSERT INTO #WAN VALUES('A','2004')
INSERT INTO #WAN VALUES('C','2004')
INSERT INTO #WAN VALUES('B','2005')
INSERT INTO #WAN VALUES('D','2005')
INSERT INTO #WAN VALUES('A','2005')
INSERT INTO #WAN VALUES('B','2004')
SELECT A.* FROM #WAN A,(
SELECT [NAME] FROM (SELECT * FROM #WAN GROUP BY [NAME],[TIME]) T
GROUP BY [NAME] HAVING COUNT([TIME])=(SELECT COUNT(*) FROM (SELECT [TIME] FROM #WAN GROUP BY [TIME]) [TIME])
) B
WHERE A.[NAME]=B.[NAME]
ORDER BY A.[TIME],A.[NAME]DROP TABLE #WAN/*
显示结果:
NAME TIME
A 2003
B 2003
A 2004
B 2004
A 2005
B 2005
*/
select a.name from
(select name , count(*) times from tab group by name) a,
(select count(distinct time) times from tab) b
where a.times = b.times)
order by time,name
CREATE TABLE #WAN(
[NAME] VARCHAR(10),
[TIME] int)
INSERT INTO #WAN VALUES('A',2003)
INSERT INTO #WAN VALUES('B',2003)
INSERT INTO #WAN VALUES('C',2003)
INSERT INTO #WAN VALUES('A',2004)
INSERT INTO #WAN VALUES('C',2004)
INSERT INTO #WAN VALUES('B',2005)
INSERT INTO #WAN VALUES('D',2005)
INSERT INTO #WAN VALUES('A',2005)
INSERT INTO #WAN VALUES('B',2004)
SELECT A.* FROM #WAN A,(
SELECT [NAME] FROM (SELECT * FROM #WAN GROUP BY [NAME],[TIME]) T
GROUP BY [NAME] HAVING COUNT([TIME])=(SELECT COUNT(*) FROM (SELECT [TIME] FROM #WAN GROUP BY [TIME]) [TIME])
) B
WHERE A.[NAME]=B.[NAME]
ORDER BY A.[TIME],A.[NAME]DROP TABLE #WAN/*
显示结果:
NAME TIME
A 2003
B 2003
A 2004
B 2004
A 2005
B 2005
*/
from tb a,(
select name from tb a
group by name
having count(*)=(select count(distinct time) from tb)
)b where a.name=b.name
create table tb(name varchar(10),time int)
insert tb select 'A',2003
union all select 'B',2003
union all select 'C',2003
union all select 'A',2004
union all select 'C',2004
union all select 'B',2005
union all select 'D',2005
union all select 'A',2005
union all select 'B',2004
goselect a.*
from tb a,(
select name from tb a
group by name
having count(*)=(select count(distinct time) from tb)
)b where a.name=b.name
order by a.time,a.name
go--删除测试
drop table tb/*--测试结果name time
---------- -----------
A 2003
B 2003
A 2004
B 2004
A 2005
B 2005(所影响的行数为 6 行)
--*/
table1--你的第一个表
table2--你的第二个表
select distinct(name) from table1 a,table2 b where a.name=b.name这样可以吗?
[NAME] VARCHAR(10),
[TIME] int)
INSERT INTO #EDW VALUES('A',2003)
INSERT INTO #EDW VALUES('B',2003)
INSERT INTO #EDW VALUES('C',2003)
INSERT INTO #EDW VALUES('A',2004)
INSERT INTO #EDW VALUES('C',2004)
INSERT INTO #EDW VALUES('B',2005)
INSERT INTO #EDW VALUES('D',2005)
INSERT INTO #EDW VALUES('A',2005)
INSERT INTO #EDW VALUES('B',2004)select a.* from #EDW a inner join (select name,sum(time) as tt from #EDW group by name having sum(time) = 6012) b
on a.name = b.name order by a.time
---------- -----------
A 2003
B 2003
A 2004
B 2004
B 2005
A 2005(6 row(s) affected)嘿嘿 把time字段改成整数型了(根据楼主情况)