表:sales
product det sales_qty
A 部門1 100
A 部門2 80
A 部門3 60
A 部門4 40
B 部門1 40
B 部門2 60
B 部門3 80
B 部門4 100
....
現在想取各種產品在各部門銷售數量最高的前2個部門,如何取?
product det sales_qty
A 部門1 100
A 部門2 80
A 部門3 60
A 部門4 40
B 部門1 40
B 部門2 60
B 部門3 80
B 部門4 100
....
現在想取各種產品在各部門銷售數量最高的前2個部門,如何取?
解决方案 »
- 控件的拖拉是創建對象嗎?
- 一个TList指针问题.昨天弄了一晚上都没有办法搞定,请高手帮忙.
- 请何判断用户切换了程序?
- 如何把一个字符串的字符逐一转换为ASCII码的8进制或16进制表示方式?
- 我的印象中Delphi 7 的界面是xp 界面,可是我安装后仍然是像Delphi 6的界面,是不是必须装到XP上才是XP样子,或者在Win 2000上还有其它的
- 这个错是怎么回事的啊!!
- 这样的grid如何做?
- Delphi自带的paradox如何设置密码?
- 请给我推荐几个不错的异步HTTP控件(不要ActiveX)
- 求救!SQL中数据库文件的修复,急急急!
- DELPHI登陆窗体怎么制作啊~!!连接数据库
- 关于Rave Designer
from ****
group by det
order by sales_sum desc
,
(
SELECT PRODUCT,MAX(SALES_QTY) AS MAXQTY FROM SALES GROUP BY PRODUCT
UNION
SELECT SALES.PRODUCT,MAX(SALES_QTY) FROM SALES LEFT JOIN (SELECT PRODUCT,MAX(SALES_QTY) AS MAXQTY FROM SALES GROUP BY PRODUCT) MAXQTY ON SALES.PRODUCT=MAXQTY.PRODUCT WHERE SALES_QTY<MAXQTY GROUP BY SALES.PRODUCT
) SELECTQTY
WHERE SALES.PRODUCT=SELECTQTY.PRODUCT AND SALES.SALES_QTY=SELECTQTY.MAXQTY
ORDER BY SALES.PRODUCT,SALES_QTY DESC一条SQL语句而已但是必须基于一点就是同一货品在不同部门之间最大数量或次之数量没有重复数量,否则是无法出现准确结果,说白了就是取第一名及第二名没有并列一说,否则你的数据库得修改
insert #t
select 'A','部門1',100
union all
select 'A','部門2',80
union all
select 'A','部門3',60
union all
select 'A','部門4', 40
union all
select 'B','部門1',40
union all
select 'B','部門2',60
union all
select 'B','部門3',80
union all
select 'B','部門4', 100--Try It
select product, det, sales_qty from
(
select t2.det,t2.sales_qty, product,(select count(1) [count] from #t t1 where t2.sales_qty<=t1.sales_qty and t1.product=t2.product) [count] from #t t2
) a
where count in (1,2)
A 部門1 100
A 部門2 80
A 部門3 60
A 部門4 40
B 部門1 40
B 部門2 60
B 部門3 80
B 部門4 100
汇总后结果:
A 部門1 100
A 部門2 80
B 部門3 80
B 部門4 100
我写一个超复杂的,能解决重复数量问题,测试数据:
+---------+-------+-----------+
| product | det | sales_qty |
+---------+-------+-----------+
| A | 部門1 | 100 |
| A | 部門2 | 80 |
| A | 部門3 | 60 |
| A | 部門4 | 40 |
| B | 部門1 | 40 |
| B | 部門2 | 60 |
| B | 部門3 | 80 |
| B | 部門4 | 100 |
| A | 部門1 | 500 |
| A | 部門2 | 150 |
| A | 部門3 | 250 |
| A | 部門4 | 350 |
| B | 部門1 | 40 |
| B | 部門2 | 60 |
| B | 部門3 | 80 |
| B | 部門4 | 100 |
+---------+-------+-----------+
先预看一下
select product,det,sum(sales_qty) as sales_sum
from sales
group by product,det
+---------+-------+-----------+
| product | det | sales_sum |
+---------+-------+-----------+
| A | 部門1 | 600 |
| A | 部門2 | 230 |
| A | 部門3 | 310 |
| A | 部門4 | 390 |
| B | 部門1 | 80 |
| B | 部門2 | 120 |
| B | 部門3 | 160 |
| B | 部門4 | 200 |
+---------+-------+-----------+结果应该为:
| A | 部門1 | 600 |
| A | 部門4 | 390 |
| B | 部門4 | 200 |
| B | 部門3 | 160 |最终sql:
select g.PRODUCT,g.det ,g.SALES_QTY from
(
select a.PRODUCT,a.det ,a.SALES_QTY from
(
select PRODUCT,det ,sum(SALES_QTY) as SALES_QTY from sales b group by PRODUCT,det
) a,
(select PRODUCT,max( SALES_QTY ) SALES_QTY from
(
select PRODUCT,det ,sum(SALES_QTY) as SALES_QTY from sales c group by PRODUCT,det
) e group by PRODUCT
)d
where a.SALES_QTY =d.SALES_QTY and a.PRODUCT=d.PRODUCTunion
select h.PRODUCT,h.det,h.SALES_QTY from
(
select PRODUCT,det ,sum(SALES_QTY) as SALES_QTY from sales b group by PRODUCT,det
) h,
(
select f.PRODUCT ,max(SALES_QTY) as SALES_QTY from
(
select a1.PRODUCT,a1.det ,a1.SALES_QTY from
(
select PRODUCT,det ,sum(SALES_QTY) as SALES_QTY from sales b1 group by PRODUCT,det
) a1,
(select PRODUCT, max( SALES_QTY ) SALES_QTY from
(
select PRODUCT,det ,sum(SALES_QTY) as SALES_QTY from sales c1 group by PRODUCT,det
) e1 group by PRODUCT
) d1
where a1.SALES_QTY <d1.SALES_QTY and a1.PRODUCT=d1.PRODUCT
) f
group by f.PRODUCT
) j
where h.SALES_QTY=j.SALES_QTY
) g
order by g.PRODUCT,g.SALES_QTY desc
结果为:
+---------+-------+-----------+
| A | 部門1 | 600 |
| A | 部門4 | 390 |
| B | 部門4 | 200 |
| B | 部門3 | 160 |
+---------+-------+-----------+
结果正确(用mysql及sqlserver测试通过)
只是太复杂,不知能否再优化一下
如果sales_qty相同,部门任意选一个??还是全列出来??如果全列出来的话
select product,det,sum(sales_qty) As sales_qty Into #T1 from [#t] group by product,detSelect * from #T1 A Where Not Exists (Select 1 from #T Where product=A.product And sales_qty>A.sales_qty Having Count(*)>1)
order by 1晕:好像在SQL论坛一样
感謝大家!
liangpei2008(Crystal) 兄的算法最直接,但是實際上有排名重復,我想在如第一名有幾個重復就隨機的取兩個,反正每產品只取兩個記錄!這樣的話又得重新考慮了。。
when '部門2' then max(sales_qty)
when '部門3' then max(sales_qty)
when '部門4' then max(sales_qty)
end as sales_qty
from #t group by product,det,sales_qty order by det,sales_qty desc
反正就是針對N個產品,N個部門
對每個產品取兩個SALES_QTY最大的。。(前兩位)