--这样? --> 测试数据: @Card declare @Card table (CardId int,CardTypeId int,CardNo int) insert into @Card select 1,1,1 union all select 2,2,2 union all select 3,3,3 union all select 4,4,4 union all select 5,5,5 union all select 6,6,6 union all select 9,1,7 union all select 10,4,8 union all select 11,6,9select a.cardid from @card a, (select top 2 CardTypeId from @Card group by CardTypeId order by count(1) desc)b where a.CardTypeId=b.CardTypeId--结果: cardid ----------- 1 4 9 10
select a.* from CardType a join ( select top 2 CardTypeId,count(*) as num from card group by CardTypeId order by count(*) desc)t on a.CardTypeId=t.CardTypeId
If not object_id('[Card]') is null Drop table [Card] Go Create table [Card]([CardId] int,[CardTypeId] int,[CardNo] int) Insert Card Select 1,1,1 union all Select 2,2,2 union all Select 3,3,3 union all Select 4,4,4 union all Select 5,5,5 union all Select 6,6,6 union all Select 9,1,7 union all Select 10,4,8 union all Select 11,6,9 Go --Select * from Card-->SQL查询如下:select top 2 cardid from card where cardtypeid in( select top 2 cardtypeid from card group by cardtypeid order by count(1) desc) order by 1 /* cardid ----------- 1 4(2 行受影响) */这样?
CREATE TABLE TBTEST(CardId INT, CardTypeId INT, CardNo INT) INSERT TBTEST SELECT 1 , 1 , 1 UNION ALL SELECT 2 , 2 , 2 UNION ALL SELECT 3 , 3 , 3 UNION ALL SELECT 4 , 4 , 4 UNION ALL SELECT 5 , 5 , 5 UNION ALL SELECT 6 , 6 , 6 UNION ALL SELECT 9 , 1 , 7 UNION ALL SELECT 10 , 4 , 8 UNION ALL SELECT 11 , 6 , 9 --DROP TABLE TBTEST SELECT CardId FROM TBTEST WHERE CardTypeId IN ( SELECT TOP 2 CardTypeId FROM TBTEST T WHERE EXISTS(SELECT 1 FROM TBTEST WHERE T.CardTypeId=CardTypeId AND T.CardId<>CardId) GROUP BY CardTypeId ORDER BY COUNT(CardTypeId) DESC) CardId ----------- 4 6 10 11(所影响的行数为 4 行)
这样能查出结果。 如果再加上个条件,cardNo是4的不计算在内应该怎么写呢?
CREATE TABLE TBTEST(CardId INT, CardTypeId INT, CardNo INT) INSERT TBTEST SELECT 1 , 1 , 1 UNION ALL SELECT 2 , 2 , 2 UNION ALL SELECT 3 , 3 , 3 UNION ALL SELECT 4 , 4 , 4 UNION ALL SELECT 5 , 5 , 5 UNION ALL SELECT 6 , 6 , 6 UNION ALL SELECT 9 , 1 , 7 UNION ALL SELECT 10 , 4 , 8 UNION ALL SELECT 11 , 6 , 9 --DROP TABLE TBTEST SELECT CardId FROM TBTEST WHERE CardTypeId IN ( SELECT top 2 CardTypeId FROM TBTEST T WHERE EXISTS(SELECT 1 FROM TBTEST WHERE T.CardTypeId=CardTypeId AND T.CardId<>CardId) GROUP BY CardTypeId ) CardId ----------- 1 4 9 10(所影响的行数为 4 行)
select top 2 cardid from card where cardtypeid in( select top 2 cardtypeid from card group by cardtypeid order by count(1) desc) and cardid<>4 order by 1 /* cardid ----------- 1 9(2 行受影响) */
CREATE TABLE TBTEST(CardId INT, CardTypeId INT, CardNo INT) INSERT TBTEST SELECT 1 , 1 , 1 UNION ALL SELECT 2 , 2 , 2 UNION ALL SELECT 3 , 3 , 3 UNION ALL SELECT 4 , 4 , 4 UNION ALL SELECT 5 , 5 , 5 UNION ALL SELECT 6 , 6 , 6 UNION ALL SELECT 9 , 1 , 7 UNION ALL SELECT 10 , 4 , 8 UNION ALL SELECT 11 , 6 , 9 --DROP TABLE TBTEST SELECT top 2 CardId FROM TBTEST WHERE CardTypeId IN ( SELECT top 2 CardTypeId FROM TBTEST T WHERE EXISTS(SELECT 1 FROM TBTEST WHERE T.CardTypeId=CardTypeId AND T.CardId<>CardId) and CardNo<>4 GROUP BY CardTypeId ) CardId ----------- 1 9(所影响的行数为 2 行)
TONY哥,楼主的需求真是难懂啊,呵呵
查出来的结果cardid 1和9的cardTypeId在表里都是1
楼主还想cardTypeId SELECT cardTypeId FROM TBTEST WHERE cardid IN ( SELECT top 2 CardId FROM TBTEST WHERE CardTypeId IN ( SELECT top 2 CardTypeId FROM TBTEST T WHERE EXISTS(SELECT 1 FROM TBTEST WHERE T.CardTypeId=CardTypeId AND T.CardId<>CardId) and CardNo<>4 GROUP BY CardTypeId ) )
select a.cardid from card a, (select top 2 CardTypeId from Card where cardno<>4 group by CardTypeId order by count(1) desc)b where a.CardTypeId=b.CardTypeId
CREATE TABLE TBTEST(CardId INT, CardTypeId INT, CardNo INT) INSERT TBTEST SELECT 1 , 1 , 1 UNION ALL SELECT 2 , 2 , 2 UNION ALL SELECT 3 , 3 , 3 UNION ALL SELECT 4 , 4 , 4 UNION ALL SELECT 5 , 5 , 5 UNION ALL SELECT 6 , 6 , 6 UNION ALL SELECT 9 , 1 , 7 UNION ALL SELECT 10 , 4 , 8 UNION ALL SELECT 11 , 6 , 9 --DROP TABLE TBTEST SELECT top 2 CardId FROM TBTEST t WHERE CardTypeId IN ( SELECT top 2 CardTypeId FROM TBTEST T WHERE EXISTS(SELECT 1 FROM TBTEST WHERE T.CardTypeId=CardTypeId AND T.CardId<>CardId) and CardNo<>4 GROUP BY CardTypeId ) and exists(select 1 from TBTEST where t.CardTypeId=CardTypeId and CardId>t.CardId)CardId ----------- 1 6(所影响的行数为 2 行)drop table tbtest
谢谢,我把你的改了下,能满足我的需求了。 select top 2 cardid from card where cardtypeid in( select top 2 cardtypeid from card where cardno<>4 group by cardtypeid order by count(1) desc) order by 1
--这样?
--> 测试数据: @Card
declare @Card table (CardId int,CardTypeId int,CardNo int)
insert into @Card
select 1,1,1 union all
select 2,2,2 union all
select 3,3,3 union all
select 4,4,4 union all
select 5,5,5 union all
select 6,6,6 union all
select 9,1,7 union all
select 10,4,8 union all
select 11,6,9select a.cardid from @card a,
(select top 2 CardTypeId from @Card group by CardTypeId order by count(1) desc)b
where a.CardTypeId=b.CardTypeId--结果:
cardid
-----------
1
4
9
10
select a.* from CardType a
join (
select top 2 CardTypeId,count(*) as num
from card
group by CardTypeId
order by count(*) desc)t
on a.CardTypeId=t.CardTypeId
Drop table [Card]
Go
Create table [Card]([CardId] int,[CardTypeId] int,[CardNo] int)
Insert Card
Select 1,1,1 union all
Select 2,2,2 union all
Select 3,3,3 union all
Select 4,4,4 union all
Select 5,5,5 union all
Select 6,6,6 union all
Select 9,1,7 union all
Select 10,4,8 union all
Select 11,6,9
Go
--Select * from Card-->SQL查询如下:select top 2 cardid
from card
where cardtypeid in(
select top 2 cardtypeid
from card
group by cardtypeid
order by count(1) desc)
order by 1
/*
cardid
-----------
1
4(2 行受影响)
*/这样?
INSERT TBTEST
SELECT 1 , 1 , 1 UNION ALL
SELECT 2 , 2 , 2 UNION ALL
SELECT 3 , 3 , 3 UNION ALL
SELECT 4 , 4 , 4 UNION ALL
SELECT 5 , 5 , 5 UNION ALL
SELECT 6 , 6 , 6 UNION ALL
SELECT 9 , 1 , 7 UNION ALL
SELECT 10 , 4 , 8 UNION ALL
SELECT 11 , 6 , 9
--DROP TABLE TBTEST
SELECT CardId FROM TBTEST WHERE CardTypeId
IN
(
SELECT TOP 2 CardTypeId FROM TBTEST T
WHERE EXISTS(SELECT 1 FROM TBTEST WHERE T.CardTypeId=CardTypeId AND T.CardId<>CardId)
GROUP BY CardTypeId
ORDER BY COUNT(CardTypeId) DESC)
CardId
-----------
4
6
10
11(所影响的行数为 4 行)
这样能查出结果。
如果再加上个条件,cardNo是4的不计算在内应该怎么写呢?
INSERT TBTEST
SELECT 1 , 1 , 1 UNION ALL
SELECT 2 , 2 , 2 UNION ALL
SELECT 3 , 3 , 3 UNION ALL
SELECT 4 , 4 , 4 UNION ALL
SELECT 5 , 5 , 5 UNION ALL
SELECT 6 , 6 , 6 UNION ALL
SELECT 9 , 1 , 7 UNION ALL
SELECT 10 , 4 , 8 UNION ALL
SELECT 11 , 6 , 9
--DROP TABLE TBTEST
SELECT CardId FROM TBTEST WHERE CardTypeId
IN
(
SELECT top 2 CardTypeId FROM TBTEST T
WHERE EXISTS(SELECT 1 FROM TBTEST WHERE T.CardTypeId=CardTypeId AND T.CardId<>CardId)
GROUP BY CardTypeId
)
CardId
-----------
1
4
9
10(所影响的行数为 4 行)
select top 2 cardid
from card
where cardtypeid in(
select top 2 cardtypeid
from card
group by cardtypeid
order by count(1) desc)
and cardid<>4
order by 1
/*
cardid
-----------
1
9(2 行受影响)
*/
INSERT TBTEST
SELECT 1 , 1 , 1 UNION ALL
SELECT 2 , 2 , 2 UNION ALL
SELECT 3 , 3 , 3 UNION ALL
SELECT 4 , 4 , 4 UNION ALL
SELECT 5 , 5 , 5 UNION ALL
SELECT 6 , 6 , 6 UNION ALL
SELECT 9 , 1 , 7 UNION ALL
SELECT 10 , 4 , 8 UNION ALL
SELECT 11 , 6 , 9
--DROP TABLE TBTEST
SELECT top 2 CardId FROM TBTEST WHERE CardTypeId
IN
(
SELECT top 2 CardTypeId FROM TBTEST T
WHERE EXISTS(SELECT 1 FROM TBTEST WHERE T.CardTypeId=CardTypeId AND T.CardId<>CardId) and CardNo<>4
GROUP BY CardTypeId
)
CardId
-----------
1
9(所影响的行数为 2 行)
查出来的结果cardid 1和9的cardTypeId在表里都是1
SELECT cardTypeId FROM TBTEST WHERE cardid
IN (
SELECT top 2 CardId FROM TBTEST WHERE CardTypeId
IN
(
SELECT top 2 CardTypeId FROM TBTEST T
WHERE EXISTS(SELECT 1 FROM TBTEST WHERE T.CardTypeId=CardTypeId AND T.CardId<>CardId) and CardNo<>4
GROUP BY CardTypeId
)
)
select a.cardid from card a,
(select top 2 CardTypeId from Card where cardno<>4 group by CardTypeId order by count(1) desc)b
where a.CardTypeId=b.CardTypeId
INSERT TBTEST
SELECT 1 , 1 , 1 UNION ALL
SELECT 2 , 2 , 2 UNION ALL
SELECT 3 , 3 , 3 UNION ALL
SELECT 4 , 4 , 4 UNION ALL
SELECT 5 , 5 , 5 UNION ALL
SELECT 6 , 6 , 6 UNION ALL
SELECT 9 , 1 , 7 UNION ALL
SELECT 10 , 4 , 8 UNION ALL
SELECT 11 , 6 , 9
--DROP TABLE TBTEST
SELECT top 2 CardId FROM TBTEST t WHERE CardTypeId
IN
(
SELECT top 2 CardTypeId FROM TBTEST T
WHERE EXISTS(SELECT 1 FROM TBTEST WHERE T.CardTypeId=CardTypeId AND T.CardId<>CardId) and CardNo<>4
GROUP BY CardTypeId
)
and exists(select 1 from TBTEST where t.CardTypeId=CardTypeId and CardId>t.CardId)CardId
-----------
1
6(所影响的行数为 2 行)drop table tbtest
谢谢,我把你的改了下,能满足我的需求了。
select top 2 cardid
from card
where cardtypeid in(
select top 2 cardtypeid
from card where cardno<>4
group by cardtypeid
order by count(1) desc)
order by 1