SELECT TerritoryID, SubTotal, SalesPersonID, SalesOrderID
FROM SalesOrderHeader AS SO1
WHERE SubTotal >=
(
SELECT MIN(SubTotal)
FROM SalesOrderHeader AS SO2
WHERE SO1.TerritoryID = SO2.TerritoryID
AND SO1.SubTotal <= SO2.SubTotal
HAVING COUNT(*) <= 5
)
ORDER BY TerritoryID,SubTotal DESC哪位高手能帮我仔细解释下这段代码? 谢谢
FROM SalesOrderHeader AS SO1
WHERE SubTotal >=
(
SELECT MIN(SubTotal)
FROM SalesOrderHeader AS SO2
WHERE SO1.TerritoryID = SO2.TerritoryID
AND SO1.SubTotal <= SO2.SubTotal
HAVING COUNT(*) <= 5
)
ORDER BY TerritoryID,SubTotal DESC哪位高手能帮我仔细解释下这段代码? 谢谢
FROM SalesOrderHeader AS SO1 ,
(SELECT
SO2.TerritoryID,
MIN(SubTotal)SubTotal
FROM
SalesOrderHeader AS SO2
group by SO2.TerritoryID
HAVING COUNT(*) <= 5) SO2 where SO1.TerritoryID=SO2.TerritoryID
and so1.SubTotal>=so2.SubTotal
ORDER BY TerritoryID,SubTotal DESC
你看懂了4楼的也就看懂了你的
SO2.TerritoryID,
MIN(SubTotal)SubTotal
FROM
SalesOrderHeader AS SO2
group by SO2.TerritoryID
HAVING COUNT(*) <= 5你先执行这句看看吧,意思是TerritoryID相同的 小于6行的就返回
但是,我把HAVING COUNT(*) <= 5改为HAVING COUNT(*) > 20仍返回一样的11行结果
如果HAVING COUNT(*)作为判定行的条件的话,为什么会返回一样的结果呢?
希望您别嫌我啰嗦,谢谢
FROM SalesOrderHeader AS SO1
WHERE SubTotal >=
(
SELECT MIN(SubTotal)
FROM SalesOrderHeader AS SO2
WHERE SO1.TerritoryID = SO2.TerritoryID
AND SO1.SubTotal <= SO2.SubTotal --大于主表销售额
HAVING COUNT(*) <= 5 --并且行数小于等于5即前五名,min(subtotal),取前五名的最小值
--如果是在前五名外的,那么将返回null值,跟null值比较,忽略主表的行
--由于使用>=比较符,即使是第一名,仍将实现=操作
)
ORDER BY TerritoryID,SubTotal DESC
--当真是高人所作啊,如果是我写,我肯定要写得面目全非了
insert @tb
select 1,1 union all
select 1,2 union all
select 1,3 union all
select 1,4 union all
select 1,5 union allselect 2,1 union all
select 2,2 union all
select 2,3 union all
select 2,4 union all
select 2,5 union all
select 2,6 SELECT id, id1
FROM @tb AS a
WHERE id1 >=
(
SELECT MIN(id1)
FROM @tb AS b
WHERE a.id = b.id
AND a.id1 <= b.id1
HAVING COUNT(*) <= 5
)select id,id1 ,
--(select count(1) from @tb where id=a.id and id1>=a.id1 ),
(select min(id1) from @tb where id=a.id and id1>=a.id1 group by id having count(1)<=5 )
from @tb a where id1>=
(select min(id1) from @tb where id=a.id and id1>=a.id1 group by id having count(1)<=5 )
--(select count(1) from @tb where id=a.id and id1>=a.id1 )<=5
--and
--id1>=(select min(id1) from @tb where id=a.id and id1>=a.id1 )/*(11 行受影响)
(1 行受影响)
id id1
----------- -----------
1 1
1 2
1 3
1 4
1 5
2 2
2 3
2 4
2 5
2 6(10 行受影响)(1 行受影响)
id id1
----------- ----------- -----------
1 1 1
1 2 2
1 3 3
1 4 4
1 5 5
2 2 2
2 3 3
2 4 4
2 5 5
2 6 6(10 行受影响)(1 行受影响)
*/这样理解吧,这样能够好理解了,
insert @tb
select 1,1 union all
select 1,2 union all
select 1,3 union all
select 1,4 union all
select 1,5 union allselect 2,1 union all
select 2,2 union all
select 2,3 union all
select 2,4 union all
select 2,5 union all
select 2,6 --SELECT id, id1
--FROM @tb AS a
--WHERE id1 >=
-- (
-- SELECT MIN(id1)
-- FROM @tb AS b
-- WHERE a.id = b.id
-- AND a.id1 <= b.id1
-- HAVING COUNT(*) <= 5
-- )select id,id1 ,
(select count(1) from @tb where id=a.id and id1>=a.id1 )排名,
(select min(id1) from @tb where id=a.id and id1>=a.id1)排名最小值,
(select min(id1) from @tb where id=a.id and id1>=a.id1 group by id having count(1)<=5 )[排名<=5最小值]
from @tb a
--where id1>=
--(select min(id1) from @tb where id=a.id and id1>=a.id1 group by id having count(1)<=5 )
--(select count(1) from @tb where id=a.id and id1>=a.id1 )<=5
--and
--id1>=(select min(id1) from @tb where id=a.id and id1>=a.id1 )/*(11 行受影响)
(1 行受影响)
id id1 排名 排名最小值 排名<=5最小值
----------- ----------- ----------- ----------- -----------
1 1 5 1 1
1 2 4 2 2
1 3 3 3 3
1 4 2 4 4
1 5 1 5 5
2 1 6 1 NULL
2 2 5 2 2
2 3 4 3 3
2 4 3 4 4
2 5 2 5 5
2 6 1 6 6(11 行受影响)(1 行受影响)*/
insert @tb
select 1,1 union all
select 1,2 union all
select 1,3 union all
select 1,4 union all
select 1,5 union allselect 2,1 union all
select 2,2 union all
select 2,3 union all
select 2,4 union all
select 2,5 union all
select 2,6 select id,id1
from @tb a
where (select count(1) from @tb where id=a.id and id1>=a.id1 )<=5/*(11 行受影响)
(1 行受影响)
id id1 排名 排名最小值 排名<=5最小值
----------- ----------- ----------- ----------- -----------
1 1 5 1 1
1 2 4 2 2
1 3 3 3 3
1 4 2 4 4
1 5 1 5 5
2 2 5 2 2
2 3 4 3 3
2 4 3 4 4
2 5 2 5 5
2 6 1 6 6(10 行受影响)(1 行受影响)*/楼主哪来的代码,这样才是最优的代码,可读性强的代码