SQL Server查询的多种书写

ChinaITOldMan 2017-12-05 02:43:33
请问以下查询有多少中写法，谢谢！

有表如下，现在统计出每个月各投资代码的当月利息收益开始等于或大于50元的那一天（每月从1号累计）

月份 投资代码 利息收益日期 利息收益
12 A 20-17-12-01 8
12 B 20-17-12-01 10
12 A 20-17-12-02 6
12 B 20-17-12-02 15
12 A 20-17-12-03 10
12 B 20-17-12-03 12
12 A 20-17-12-04 10
12 B 20-17-12-04 10
12 A 20-17-12-05 10
12 B 20-17-12-05 10
12 A 20-17-12-06 20
12 B 20-17-12-06 15
12 A 20-17-12-07 12
12 B 20-17-12-07 10

本例子结果如下：
月份 投资代码 等于或大于50的第一天
12 A 2017/12/6
12 B 2017/12/5

...全文
317 18 打赏 收藏 转发到动态 举报
写回复
用AI写文章
18 条回复
切换为时间正序
请发表友善的回复…
发表回复
日月路明 2017-12-08
  • 打赏
  • 举报
 回复
如果这个需求是长期性的，可以直接加字段来完成，否则效率始终不高，也可以用如下代码实现 
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL
DROP TABLE t
GO
CREATE TABLE t(
    月份 int
    ,投资代码 NVARCHAR(20)
    ,利息收益日期 datetime
    ,利息收益      int
)
GO
INSERT INTO t
          select  12, 'A', '2017-12-01', 8
union all select  12, 'B', '2017-12-01', 10
union all select  12, 'A', '2017-12-02', 6
union all select  12, 'B', '2017-12-02', 15
union all select  12, 'A', '2017-12-03', 10
union all select  12, 'B', '2017-12-03', 12
union all select  12, 'A', '2017-12-04', 10
union all select  12, 'B', '2017-12-04', 10
union all select  12, 'A', '2017-12-05', 10
union all select  12, 'B', '2017-12-05', 10
union all select  12, 'A', '2017-12-06', 20
union all select  12, 'B', '2017-12-06', 15
union all select  12, 'A', '2017-12-07', 12
union all select  12, 'B', '2017-12-07', 10


select 月份,投资代码,min(利息收益日期)
from(
    select *,TJe=(Select sum(利息收益) from t as t2 where t2.月份=t.月份 and t2.投资代码=t.投资代码 and t2.利息收益日期<=t.利息收益日期)
      from t
    )as a
where Tje>=50
group by 月份,投资代码
ChinaITOldMan 2017-12-06
  • 打赏
  • 举报
 回复
引用 15 楼 zjcxc 的回复:
借用楼上的临时表 
SELECT * FROM #t a
WHERE EXISTS(
	SELECT 1 FROM #t b
	WHERE a.月份 = b.月份 AND a.投资代码 = b.投资代码
		AND b.利息收益日期 < a.利息收益日期
	HAVING SUM(b.利息收益) < 50 AND SUM(b.利息收益) + a.利息收益 >= 50
)
谢谢大版主！
zjcxc 2017-12-06
  • 打赏
  • 举报
 回复
借用楼上的临时表 
SELECT * FROM #t a
WHERE EXISTS(
	SELECT 1 FROM #t b
	WHERE a.月份 = b.月份 AND a.投资代码 = b.投资代码
		AND b.利息收益日期 < a.利息收益日期
	HAVING SUM(b.利息收益) < 50 AND SUM(b.利息收益) + a.利息收益 >= 50
)
lipiaolpt 2017-12-06
  • 打赏
  • 举报
 回复
学习学习！！！
ChinaITOldMan 2017-12-05
  • 打赏
  • 举报
 回复
引用 13 楼 RINK_1 的回复:

IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO
CREATE TABLE #T(
    月份 int
    ,投资代码 NVARCHAR(20)
    ,利息收益日期 datetime
    ,利息收益      int
)
GO
INSERT INTO #T
          select  12, 'A', '2017-12-01', 8
union all select  12, 'B', '2017-12-01', 10
union all select  12, 'A', '2017-12-02', 6
union all select  12, 'B', '2017-12-02', 15
union all select  12, 'A', '2017-12-03', 10
union all select  12, 'B', '2017-12-03', 12
union all select  12, 'A', '2017-12-04', 10
union all select  12, 'B', '2017-12-04', 10
union all select  12, 'A', '2017-12-05', 10
union all select  12, 'B', '2017-12-05', 10
union all select  12, 'A', '2017-12-06', 20
union all select  12, 'B', '2017-12-06', 15
union all select  12, 'A', '2017-12-07', 12
union all select  12, 'B', '2017-12-07', 10

select * from #T A
where 
exists (select 1 from #T where 利息收益日期<A.利息收益日期 
                         and A.投资代码=投资代码 
                         and month(利息收益日期)=month(A.利息收益日期)
                 group by 投资代码,MONTH(利息收益日期) 
                 HAVING SUM(利息收益)<50)
and 
exists (select 1 from #T where 利息收益日期<=A.利息收益日期
                         and A.投资代码=投资代码 
                         and month(利息收益日期)=month(A.利息收益日期)
                 group by 投资代码,MONTH(利息收益日期) 
                 HAVING SUM(利息收益)>=50)
谢谢您，这个结果是正确的
RINK_1 2017-12-05
  • 打赏
  • 举报
 回复 

IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO
CREATE TABLE #T(
    月份 int
    ,投资代码 NVARCHAR(20)
    ,利息收益日期 datetime
    ,利息收益      int
)
GO
INSERT INTO #T
          select  12, 'A', '2017-12-01', 8
union all select  12, 'B', '2017-12-01', 10
union all select  12, 'A', '2017-12-02', 6
union all select  12, 'B', '2017-12-02', 15
union all select  12, 'A', '2017-12-03', 10
union all select  12, 'B', '2017-12-03', 12
union all select  12, 'A', '2017-12-04', 10
union all select  12, 'B', '2017-12-04', 10
union all select  12, 'A', '2017-12-05', 10
union all select  12, 'B', '2017-12-05', 10
union all select  12, 'A', '2017-12-06', 20
union all select  12, 'B', '2017-12-06', 15
union all select  12, 'A', '2017-12-07', 12
union all select  12, 'B', '2017-12-07', 10

select * from #T A
where 
exists (select 1 from #T where 利息收益日期<A.利息收益日期 
                         and A.投资代码=投资代码 
                         and month(利息收益日期)=month(A.利息收益日期)
                 group by 投资代码,MONTH(利息收益日期) 
                 HAVING SUM(利息收益)<50)
and 
exists (select 1 from #T where 利息收益日期<=A.利息收益日期
                         and A.投资代码=投资代码 
                         and month(利息收益日期)=month(A.利息收益日期)
                 group by 投资代码,MONTH(利息收益日期) 
                 HAVING SUM(利息收益)>=50)
ChinaITOldMan 2017-12-05
  • 打赏
  • 举报
 回复
引用 11 楼 yenange 的回复:
嗯，主要是因为你这个是累加后达到指定数目的最小值， 说到累加就必须有类似行号一样的东西，而且必须排序。 几重限制之下， 不用开窗函数就很困难了， 如果用循环什么的， 效率太低得不偿失。
明白，谢谢您！ 只是想知道是否有其它的写法。
吉普赛的歌 2017-12-05
  • 打赏
  • 举报
 回复
嗯，主要是因为你这个是累加后达到指定数目的最小值， 说到累加就必须有类似行号一样的东西，而且必须排序。 几重限制之下， 不用开窗函数就很困难了， 如果用循环什么的， 效率太低得不偿失。
ChinaITOldMan 2017-12-05
  • 打赏
  • 举报
 回复
引用 9 楼 yenange 的回复:
[quote=引用 7 楼 mcxhh2005 的回复:] 谢谢版主, 若不用开窗函数over是否还有其他方法，谢谢！
你的版本低于SQL Server2012用不了是吗？试下下面的代码， 下面的在 SQL Server 2005 上运行无误。 
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL
DROP TABLE t
GO
CREATE TABLE t(
	月份 int
	,投资代码 NVARCHAR(20)
	,利息收益日期 datetime
	,利息收益	  int
)
GO
INSERT INTO t
          select  12, 'A', '2017-12-01', 8
union all select  12, 'B', '2017-12-01', 10
union all select  12, 'A', '2017-12-02', 6
union all select  12, 'B', '2017-12-02', 15
union all select  12, 'A', '2017-12-03', 10
union all select  12, 'B', '2017-12-03', 12
union all select  12, 'A', '2017-12-04', 10
union all select  12, 'B', '2017-12-04', 10
union all select  12, 'A', '2017-12-05', 10
union all select  12, 'B', '2017-12-05', 10
union all select  12, 'A', '2017-12-06', 20
union all select  12, 'B', '2017-12-06', 15
union all select  12, 'A', '2017-12-07', 12
union all select  12, 'B', '2017-12-07', 10

;with cte as(
	SELECT
		row_number() over(partition by 月份,投资代码 order by 利息收益日期 asc) as rid 
		,*
	FROM t
)
,cte2 as (
	select * from cte where rid=1
	union all
	select a.rid,a.月份,a.投资代码,a.利息收益日期,a.利息收益+b.利息收益 as 利息收益 
	from cte as a inner join cte2 b on a.rid=b.rid+1 and a.月份=b.月份 and a.投资代码=b.投资代码
)
select 
	月份
	,投资代码
	,min(利息收益日期) as 利息收益日期
	,min(利息收益) as 利息收益Total
from cte2 
where 利息收益>=50
group by 月份,投资代码
/*
月份          投资代码           利息收益日期            利息收益Total
----------- -------------------- ----------------------- -----------
12          A                    2017-12-06 00:00:00.000 64
12          B                    2017-12-05 00:00:00.000 57
*/
[/quote] 谢谢您热情的帮助，我是在SQL Sever2008中执行的，是可以执行的，我是看是否其他的写法，多谢！
吉普赛的歌 2017-12-05
  • 打赏
  • 举报
 回复
引用 7 楼 mcxhh2005 的回复:
谢谢版主, 若不用开窗函数over是否还有其他方法，谢谢！
你的版本低于SQL Server2012用不了是吗？试下下面的代码， 下面的在 SQL Server 2005 上运行无误。 
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL
DROP TABLE t
GO
CREATE TABLE t(
	月份 int
	,投资代码 NVARCHAR(20)
	,利息收益日期 datetime
	,利息收益	  int
)
GO
INSERT INTO t
          select  12, 'A', '2017-12-01', 8
union all select  12, 'B', '2017-12-01', 10
union all select  12, 'A', '2017-12-02', 6
union all select  12, 'B', '2017-12-02', 15
union all select  12, 'A', '2017-12-03', 10
union all select  12, 'B', '2017-12-03', 12
union all select  12, 'A', '2017-12-04', 10
union all select  12, 'B', '2017-12-04', 10
union all select  12, 'A', '2017-12-05', 10
union all select  12, 'B', '2017-12-05', 10
union all select  12, 'A', '2017-12-06', 20
union all select  12, 'B', '2017-12-06', 15
union all select  12, 'A', '2017-12-07', 12
union all select  12, 'B', '2017-12-07', 10

;with cte as(
	SELECT
		row_number() over(partition by 月份,投资代码 order by 利息收益日期 asc) as rid 
		,*
	FROM t
)
,cte2 as (
	select * from cte where rid=1
	union all
	select a.rid,a.月份,a.投资代码,a.利息收益日期,a.利息收益+b.利息收益 as 利息收益 
	from cte as a inner join cte2 b on a.rid=b.rid+1 and a.月份=b.月份 and a.投资代码=b.投资代码
)
select 
	月份
	,投资代码
	,min(利息收益日期) as 利息收益日期
	,min(利息收益) as 利息收益Total
from cte2 
where 利息收益>=50
group by 月份,投资代码
/*
月份          投资代码           利息收益日期            利息收益Total
----------- -------------------- ----------------------- -----------
12          A                    2017-12-06 00:00:00.000 64
12          B                    2017-12-05 00:00:00.000 57
*/
ChinaITOldMan 2017-12-05
  • 打赏
  • 举报
 回复
引用 2 楼 zjcxc 的回复:
with data as(select * from(values
(12, 'A', '20-17-12-01', 8),
(12, 'B', '20-17-12-01', 10),
(12, 'A', '20-17-12-02', 6),
(12, 'B', '20-17-12-02', 15),
(12, 'A', '20-17-12-03', 10),
(12, 'B', '20-17-12-03', 12),
(12, 'A', '20-17-12-04', 10),
(12, 'B', '20-17-12-04', 10),
(12, 'A', '20-17-12-05', 10),
(12, 'B', '20-17-12-05', 10),
(12, 'A', '20-17-12-06', 20),
(12, 'B', '20-17-12-06', 15),
(12, 'A', '20-17-12-07', 12),
(12, 'B', '20-17-12-07', 10)
)x(月份,投资代码,利息收益日期,利息收益))
select 月份,投资代码, min(case when _sum >=50 then 利息收益日期 end)
from(
	select *, _sum=sum(利息收益) over(partition by 月份,投资代码 order by 利息收益日期)
	from data
) xx
group by 月份,投资代码
老大，谢谢指点 1. 请问有不用Over函数的写法没？ 2. 之前买过你写的SQL Server2000与SQL Sever2005，请问你还有其他新书没？期待中
ChinaITOldMan 2017-12-05
  • 打赏
  • 举报
 回复
引用 6 楼 yenange 的回复:
--注：需要 SQL Server2012 或以上版本才能运行
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL
DROP TABLE t
GO
CREATE TABLE t(
	月份 int
	,投资代码 NVARCHAR(20)
	,利息收益日期 datetime
	,利息收益	  int
)
GO
INSERT INTO t
          select  12, 'A', '2017-12-01', 8
union all select  12, 'B', '2017-12-01', 10
union all select  12, 'A', '2017-12-02', 6
union all select  12, 'B', '2017-12-02', 15
union all select  12, 'A', '2017-12-03', 10
union all select  12, 'B', '2017-12-03', 12
union all select  12, 'A', '2017-12-04', 10
union all select  12, 'B', '2017-12-04', 10
union all select  12, 'A', '2017-12-05', 10
union all select  12, 'B', '2017-12-05', 10
union all select  12, 'A', '2017-12-06', 20
union all select  12, 'B', '2017-12-06', 15
union all select  12, 'A', '2017-12-07', 12
union all select  12, 'B', '2017-12-07', 10

SELECT 
	*
	,sum(利息收益) over(partition by 月份,投资代码 order by 利息收益日期 asc) as sum_step
FROM t
/*
月份          投资代码           利息收益日期            利息收益    sum_step
----------- -------------------- ----------------------- ----------- -----------
12          A                    2017-12-01 00:00:00.000 8           8
12          A                    2017-12-02 00:00:00.000 6           14
12          A                    2017-12-03 00:00:00.000 10          24
12          A                    2017-12-04 00:00:00.000 10          34
12          A                    2017-12-05 00:00:00.000 10          44
12          A                    2017-12-06 00:00:00.000 20          64
12          A                    2017-12-07 00:00:00.000 12          76
12          B                    2017-12-01 00:00:00.000 10          10
12          B                    2017-12-02 00:00:00.000 15          25
12          B                    2017-12-03 00:00:00.000 12          37
12          B                    2017-12-04 00:00:00.000 10          47
12          B                    2017-12-05 00:00:00.000 10          57
12          B                    2017-12-06 00:00:00.000 15          72
12          B                    2017-12-07 00:00:00.000 10          82
*/

select  月份
		,投资代码
		,min(利息收益日期) as [利息收益日期]
		,min(sum_step) as [利息收益Total]
	from (
		SELECT 
			*
			,sum(利息收益) over(partition by 月份,投资代码 order by 利息收益日期 asc) as sum_step
		FROM t
	) as t
where sum_step>=50
group by 月份,投资代码
/*
月份          投资代码           利息收益日期            利息收益Total
----------- -------------------- ----------------------- -----------
12          A                    2017-12-06 00:00:00.000 64
12          B                    2017-12-05 00:00:00.000 57
*/
谢谢版主, 若不用开窗函数over是否还有其他方法，谢谢！
吉普赛的歌 2017-12-05
  • 打赏
  • 举报
 回复 
--注：需要 SQL Server2012 或以上版本才能运行

USE tempdb

GO

IF OBJECT_ID('t') IS NOT NULL

DROP TABLE t

GO

CREATE TABLE t(

	月份 int

	,投资代码 NVARCHAR(20)

	,利息收益日期 datetime

	,利息收益	  int

)

GO

INSERT INTO t

          select  12, 'A', '2017-12-01', 8

union all select  12, 'B', '2017-12-01', 10

union all select  12, 'A', '2017-12-02', 6

union all select  12, 'B', '2017-12-02', 15

union all select  12, 'A', '2017-12-03', 10

union all select  12, 'B', '2017-12-03', 12

union all select  12, 'A', '2017-12-04', 10

union all select  12, 'B', '2017-12-04', 10

union all select  12, 'A', '2017-12-05', 10

union all select  12, 'B', '2017-12-05', 10

union all select  12, 'A', '2017-12-06', 20

union all select  12, 'B', '2017-12-06', 15

union all select  12, 'A', '2017-12-07', 12

union all select  12, 'B', '2017-12-07', 10



SELECT 

	*

	,sum(利息收益) over(partition by 月份,投资代码 order by 利息收益日期 asc) as sum_step

FROM t

/*

月份          投资代码           利息收益日期            利息收益    sum_step

----------- -------------------- ----------------------- ----------- -----------

12          A                    2017-12-01 00:00:00.000 8           8

12          A                    2017-12-02 00:00:00.000 6           14

12          A                    2017-12-03 00:00:00.000 10          24

12          A                    2017-12-04 00:00:00.000 10          34

12          A                    2017-12-05 00:00:00.000 10          44

12          A                    2017-12-06 00:00:00.000 20          64

12          A                    2017-12-07 00:00:00.000 12          76

12          B                    2017-12-01 00:00:00.000 10          10

12          B                    2017-12-02 00:00:00.000 15          25

12          B                    2017-12-03 00:00:00.000 12          37

12          B                    2017-12-04 00:00:00.000 10          47

12          B                    2017-12-05 00:00:00.000 10          57

12          B                    2017-12-06 00:00:00.000 15          72

12          B                    2017-12-07 00:00:00.000 10          82

*/



select  月份

		,投资代码

		,min(利息收益日期) as [利息收益日期]

		,min(sum_step) as [利息收益Total]

	from (

		SELECT 

			*

			,sum(利息收益) over(partition by 月份,投资代码 order by 利息收益日期 asc) as sum_step

		FROM t

	) as t

where sum_step>=50

group by 月份,投资代码

/*

月份          投资代码           利息收益日期            利息收益Total

----------- -------------------- ----------------------- -----------

12          A                    2017-12-06 00:00:00.000 64

12          B                    2017-12-05 00:00:00.000 57

*/
ChinaITOldMan 2017-12-05
  • 打赏
  • 举报
 回复
select 月份, 投资代码 ,(select Max(利息收益日期) from tb b where a.月份=b.月份, a.投资代码=b.投资代码 group by 月份, 投资代码 having sum(利息收益)>=50 from tb a 好象用上面类似方法写，但是上面代码不对，请哪位大侠帮写下，谢谢！
zjcxc 2017-12-05
  • 打赏
  • 举报
 回复 
with data as(select * from(values
(12, 'A', '20-17-12-01', 8),
(12, 'B', '20-17-12-01', 10),
(12, 'A', '20-17-12-02', 6),
(12, 'B', '20-17-12-02', 15),
(12, 'A', '20-17-12-03', 10),
(12, 'B', '20-17-12-03', 12),
(12, 'A', '20-17-12-04', 10),
(12, 'B', '20-17-12-04', 10),
(12, 'A', '20-17-12-05', 10),
(12, 'B', '20-17-12-05', 10),
(12, 'A', '20-17-12-06', 20),
(12, 'B', '20-17-12-06', 15),
(12, 'A', '20-17-12-07', 12),
(12, 'B', '20-17-12-07', 10)
)x(月份,投资代码,利息收益日期,利息收益))
select 月份,投资代码, min(case when _sum >=50 then 利息收益日期 end)
from(
	select *, _sum=sum(利息收益) over(partition by 月份,投资代码 order by 利息收益日期)
	from data
) xx
group by 月份,投资代码
听雨停了 2017-12-05
  • 打赏
  • 举报
 回复
有多少种写法这个鬼知道具体有多少种啊。你这问题就好比我有一百块钱，你觉得我有多少种花法。反正我知道的最简单的办法就是用递归
北邮数据库实验8 数据查询分析优化实验
1. 在SQL Server Management Studio平台下，通过观察Select/Insert/delete/update等SQL语句的查询执行计划，分析查询执行计划中连接、选择、投影等关系代数操作的实现方式及其执行成本。熟悉了解SQL SERVER数据库中查询优化的使用，理解数据库查询优化的基本概念。 2. 掌握利用SQL Server Management Studio提供的机制，分析对比形式不同、执行结果等价的不同SQL语句的查询执行计划的执行成本和执行时间差异。 3. 熟悉了解视图和with临时视图的创建，观察视图查询、with临时视图查询的执行计划。 4. 参照文档“数据库物理设计及查询优化”中SQL语句查询优化相关内容，在多种情况下，对比实现方式不同但查询结果相同的等价SQL语句在执行计划和成本方面的差异，加深对查询优化的理解，进行书写优化SQL语句的初步训练，提高编写高效SQL语句的能力。涉及以下几方面：
c#数据库后台连接层
Access,MySql,Oracle,SqlServer多种数据库后台操作，包括添加，修改，查询，删除，编写规范，直接调用。书写采用存储过程方式进行，代码严谨，书写工整，源代码和多种使用样例。
SQLServer 中提高查询性能的方法
MS   SQL   Server查询优化方法 查询速度慢的原因很多，常见如下几种           1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷)           2、I/O吞吐量小，形成了瓶颈效应。           3、没有创建计算列导致查询不优化。           4、内存不足           5、网络速度慢           
SQLServer 查看依赖关系
13. 查看数据库对象间的依赖关系 在SQL Server中，(可编程)对象间的引用即依赖关系，有多种方式可以检查，随着版本变更，方式也有所不同。 父子关系的对象，不通过依赖关系来查询，比如： 1. 外键关系 use tempdb GO --drop table tb1,tb2 create table tb1 ( col1 int Primary key,
sqlserver 中提高查询性能的方法

MS   SQL   Server查询优化方法
查询速度慢的原因很多，常见如下几种 

        1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷) 
        2、I/O吞吐量小，形成了瓶颈效应。 
        3、没有创建计算列导致查询不优化。 
        4、内存不足 
        5、网络速度慢 
        6、查询出的数据量过大（可以采用多次
疑难问题

22,207

社区成员

121,730

社区内容

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

加载中

查看更多榜单
社区公告
暂无公告

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

+ 用AI写文章