求教一个SQL问题

dmlhello 2017-02-09 09:01:38
如题 两个表 这个问题2的SQL该怎么写

...全文
149 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
dmlhello 2017-02-11
  • 打赏
  • 举报
回复
引用 4 楼 sinat_28984567 的回复:
没看清是按amout取前五,修改了一下:
---测试数据
;WITH payment(id,customer_id,amout,other)AS(
select 1,1,10,'测试1'  union all 
select 2,2,20,'测试2'  union all 
select 3,2,30,'测试3'  union all 
select 4,3,40,'测试4'  union all 
select 5,3,50,'测试5'  union all 
select 6,4,60,'测试6'  union all 
select 7,5,70,'测试7'  union all 
select 8,5,80,'测试8'  union all 
select 9,6,90,'测试8'  union all 
select 10,7,100,'测试10' 
),Customer(customer_id, first_name)AS(
select 1,'Ja'  union all 
select 2,'Jb'  union all 
select 3,'Jc'  union all 
select 4,'Jd'  union all 
select 5,'Je'  union all 
select 6,'Jf'  union all 
select 7,'Ga'  union all 
select 8,'Qa'  
)
---测试数据结束,读取语句如下:
SELECT TOP 5
        b.first_name ,
        ( SELECT    SUM(a.amout)
          FROM      payment a
          WHERE     a.customer_id = b.customer_id
        ) AS amount
FROM    Customer b
WHERE   SUBSTRING(b.first_name, 1, 1) = 'J'
ORDER BY amount DESC;
谢谢
二月十六 2017-02-10
  • 打赏
  • 举报
回复
没看清是按amout取前五,修改了一下:
---测试数据
;WITH payment(id,customer_id,amout,other)AS(
select 1,1,10,'测试1'  union all 
select 2,2,20,'测试2'  union all 
select 3,2,30,'测试3'  union all 
select 4,3,40,'测试4'  union all 
select 5,3,50,'测试5'  union all 
select 6,4,60,'测试6'  union all 
select 7,5,70,'测试7'  union all 
select 8,5,80,'测试8'  union all 
select 9,6,90,'测试8'  union all 
select 10,7,100,'测试10' 
),Customer(customer_id, first_name)AS(
select 1,'Ja'  union all 
select 2,'Jb'  union all 
select 3,'Jc'  union all 
select 4,'Jd'  union all 
select 5,'Je'  union all 
select 6,'Jf'  union all 
select 7,'Ga'  union all 
select 8,'Qa'  
)
---测试数据结束,读取语句如下:
SELECT TOP 5
        b.first_name ,
        ( SELECT    SUM(a.amout)
          FROM      payment a
          WHERE     a.customer_id = b.customer_id
        ) AS amount
FROM    Customer b
WHERE   SUBSTRING(b.first_name, 1, 1) = 'J'
ORDER BY amount DESC;
Andy-W 2017-02-09
  • 打赏
  • 举报
回复
try:

select top(5) b.first_name,sum(a.amount) as amount  
	from payment a 
	inner join Customer b on b.customer_id=a.customer_id
	where b.first_name like 'J%'
	group by a.customer_id,b.first_name 
	order by sum(a.amount) desc
二月十六 2017-02-09
  • 打赏
  • 举报
回复
上边的语句粘贴少了点。。。 语句如下:
---测试数据
;WITH payment(id,customer_id,amout,other)AS(
select 1,1,10,'测试1'  union all 
select 2,2,20,'测试2'  union all 
select 3,2,30,'测试3'  union all 
select 4,3,40,'测试4'  union all 
select 5,3,50,'测试5'  union all 
select 6,4,60,'测试6'  union all 
select 7,5,70,'测试7'  union all 
select 8,5,80,'测试8'  union all 
select 9,6,90,'测试8'  union all 
select 10,7,100,'测试10' 
),Customer(customer_id, first_name)AS(
select 1,'Ja'  union all 
select 2,'Jb'  union all 
select 3,'Jc'  union all 
select 4,'Jd'  union all 
select 5,'Je'  union all 
select 6,'Jf'  union all 
select 7,'Ga'  union all 
select 8,'Qa'  
)
---测试数据结束,读取语句如下:
SELECT TOP 5
        b.first_name ,
        ( SELECT    SUM(a.amout)
          FROM      payment a
          WHERE     a.customer_id = b.customer_id
        ) AS amount
FROM    Customer b
WHERE   SUBSTRING(b.first_name, 1, 1) = 'J'
ORDER BY b.customer_id;
二月十六 2017-02-09
  • 打赏
  • 举报
回复
语句:
select 4,3,40,'测试4'  union all 
select 5,3,50,'测试5' union all
select 6,4,60,'测试6' union all
select 7,5,70,'测试7' union all
select 8,5,80,'测试8' union all
select 9,6,90,'测试8' union all
select 10,7,100,'测试10'
),Customer(customer_id, first_name)AS(
select 1,'Ja' union all
select 2,'Jb' union all
select 3,'Jc' union all
select 4,'Jd' union all
select 5,'Je' union all
select 6,'Jf' union all
select 7,'Ga' union all
select 8,'Qa'
)
---测试数据结束,读取语句如下:
SELECT TOP 5
b.first_name ,
( SELECT SUM(a.amout)
FROM payment a
WHERE a.customer_id = b.customer_id
) AS amount
FROM Customer b
WHERE SUBSTRING(b.first_name, 1, 1) = 'J'
ORDER BY b.customer_id;


结果:







22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧