SQL Server查询语句 [英] SQL Server Query statement
本文介绍了SQL Server查询语句的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
嗨专家，
我在SQL Server表'tblLeave'中有一些如下数据： -
PAYCODE LV_TYPE FROM_DATE TO_DATE LVALUE
5023 SL 14/12/2012 0:00 14/12/2012 0:00 1
5023 SL 15/12 / 2012 0:00 15/12/2012 0:00 1
5023 COF 16/12/2012 0:00 16/12/2012 0:00 1
5023 SL 19/12/2012 0： 00 19/12/2012 0:00 1
5023 SL 22/12/2012 0:00 22/12/2012 0:00 1
5023 SL 23/12/2012 0:00 23/12 / 2012 0:00 1
5023 SL 24/12/2012 0:00 24/12/2012 0:00 1
5023 PL 28/12/2012 0:00 28/12/2012 0： 00 1
5023 PL 29/12/2012 0:00 29/12/2012 0:00 1
5023 PL 30/12/2012 0:00 30/12/2012 0:00 1
5023 PL 31/12/2012 0:00 31/12/2012 0:00 1
查询结果必须如下： -
PAYCODE LV_TYPE FROM_DATE TO_DATE LVALUE
5023 SL 14/12/2012 0:00 15/12/2012 0:00 2
5023 COF 16/12 / 2012 0:00 16/12/2012 0:00 1
5023 SL 19/12/2012 0:00 19/12/2012 0:00 1
5023 SL 22/12/2012 0:00 24/12/2012 0:00 3
5023 PL 28/12/2012 0:00 31/12/2012 0:00 4
如果连续休假相同，则应合并为一行，提及from_date至to_date。
请帮帮我。
谢谢，
Anil Kumar
添加了代码块 - OriginalGriff [/ edit]
解决方案
检查一下。
SELECT LV_TYPE，LV_TYPE，MIN（FROM_DATE） AS FROM_DATE，MAX（TO_DATE） AS TO_DATE，COUNT（LVALUEP） AS LVALUE
FROM tblLeave
GROUP BY PAYCODE，LV_TYPE
请尝试以下
SELECT PAYCODE，LV_TYPE，MIN（ FROM_DATE） AS FROM_DATE，
MAX（FROM_DATE） AS TO_DATE，COUNT（' A'） AS LVALUE
FROM （
SELECT PAYCODE，LV_TYPE，FROM_DATE，
DATEDIFF（D，ROW_NUMBER（） OVER （ ORDER BY FROM_DATE），FROM_DATE ） AS DtRange
FROM tblLeave） AS dt
GROUP BY PAYCODE，LV_TYPE，DtRange
ORDER BY FROM _DATE
PAYCODE LV_TYPE FROM_DATE TO_DATE LVALUE
----------- ------ - ---------- ---------- -----------
5023 SL 2012-12-14 2012-12-15 2
5023 COF 2012-12-16 2012-12-16 1
5023 SL 2012-12-19 2012-12-19 1
5023 SL 2012-12-22 2012-12-24 3
5023 PL 2012-12-28 2012-12-31 4
我测试了解决方案1并且它不返回您需要的结果集。
下面的存储过程已经过测试，并返回您在示例中显示的结果集。在SQL Server数据库中创建此存储过程并在程序中执行存储过程。存储过程将返回行，如示例中所示。
可能会有一些奇特的SQL语句会返回相同的结果，但我无法弄明白。我通读了我的 Jim Celko的SQL for Smarties 一书，这种类型的查询不在那里。我欢迎其他可能比我更了解SQL的贡献者提交一个在单个SQL语句而不是存储过程算法中完成的解决方案。
USE [YourDatabaseNameGoesHere]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
- ========= ====================================
- 作者：Mike Meinz
- 创建日期：2013年1月26日
- 描述：总结离开行
- ========================= ====================
CREATE PROCEDURE SUMMARIZE_LEAVE_ROWS
AS
BEGIN
- 添加SET NOCOUNT ON以防止额外的来自
的结果集 - 干扰SELECT语句。
SET NOCOUNT ON;
DECLARE @curPayCode int，@ curLV_Type varchar（3），@ curFrom_Date date，@ curTo_Date date，@ curLValue int
DECLARE @lastPayCode int，@ lastLV_Type varchar（3），@ lastFrom_Date date，@ lastTo_Date date ，@ lastLValue int
DECLARE @SummarizedRowsTable TABLE（PayCode int，LV_Type varchar（3），From_date date，To_Date date，lvalue int）
-
- 从tblLeave $ b $中选择行b声明Summary_Leave_Cursor CURSOR FOR
SELECT PayCode，LV_Type，From_Date，To_Date，lvalue from tblLeave order by PayCode，From_date，LV_Type
DECLARE @FIRST_TIME BIT
SET @ FIRST_TIME = 1
OPEN Summary_Leave_Cursor
- 获取第一行
FETCH NEXT FROM Summary_Leave_Cursor INTO @ curPayCode，@ curLV_Type，@ curFrom_date，@ curTo_Date，@ curLValue
WHILE @@ FETCH_STATUS = 0
BEGIN
IF @ FIRST_TIME = 1
BEGIN
SET @ FIRST_TIME = 0
- 保存当前行数据
SET @ lastPayCode = @ curPayCode
SET @ lastLV_Type = @curLV_Type
SET @ lastFrom_Date = @ curFrom_Date
SET @lastTo_ Date = @ curTo_Date
SET @ lastLValue = @ curLValue
END
ELSE
BEGIN
- 检查相同的PayCode和离开类型以及当前的FromDate是否晚于最后ToDate
- 使用DATEDIFF和DATEADD比较日期
- 如果需要考虑时间，则必须更改
IF @ curPayCode = @ lastPayCode AND @ curLV_Type = @ lastLV_Type AND DATEDIFF（day，@ CurFrom_date，DATEADD（day，1，@ lastTo_date））= 0
BEGIN
- 累积LValue
SET @ lastlvalue = @ lastlvalue + @ curlvalue
- 使用新的ToDate更新ToDate
SET @ lastTo_Date = @ curTo_Date
END
ELSE
BEGIN
- 将汇总数据插入到临时表
INSERT INTO @SummarizedRowsTable（PayCode，LV_Type，From_Date，To_Date，lvalue）值（@lastPayCode，@ lastLV_Type，@ lastFrom_Date，@ lastTo_Date，@ lastLValue）;
- 保存当前行数据
SET @ lastPayCode = @ curPayCode
SET @ lastLV_Type = @ curLV_Type
SET @ lastFrom_Date = @ curFrom_Date
SET @ lastTo_Date = @ curTo_Date
SET @ lastLValue = @ curLValue
END
END
FETCH NEXT FROM Summary_Leave_Cursor INTO @ curPayCode，@ curLV_Type，@ curFrom_date，@ curTo_Date，@ curLValue;
END
- 将最后一个汇总行插入临时表
INSERT INTO @SummarizedRowsTable（PayCode，LV_Type，From_Date，To_Date，lvalue）值（@lastPayCode，@ lastLV_Type，@ lastFrom_Date， @ lastTo_Date，@ lastLValue）;
关闭Summary_Leave_Cursor;
DEALLOCATE Summary_Leave_Cursor;
-
- 返回临时表作为存储过程结果
SELECT PayCode，LV_Type，From_Date，To_Date，Lvalue FROM @SummarizedRowsTable order by PayCode，From_Date，LV_Type;
结束
这是我用来测试存储过程的表设计：
CREATE TABLE [dbo]。[tblLeave]（
[PayCode] [int] NOT NULL，
[LV_Type] [varchar]（3）NOT NULL，
[ From_date] [date] NOT NULL，
[To_Date] [date] NOT NULL，
[LValue] [int] NOT NULL
）ON [PRIMARY]
Hi Experts,
I have some data like below in SQL Server Table ''tblLeave'': -
PAYCODE LV_TYPE FROM_DATE TO_DATE LVALUE
5023 SL 14/12/2012 0:00 14/12/2012 0:00 1
5023 SL 15/12/2012 0:00 15/12/2012 0:00 1
5023 COF 16/12/2012 0:00 16/12/2012 0:00 1
5023 SL 19/12/2012 0:00 19/12/2012 0:00 1
5023 SL 22/12/2012 0:00 22/12/2012 0:00 1
5023 SL 23/12/2012 0:00 23/12/2012 0:00 1
5023 SL 24/12/2012 0:00 24/12/2012 0:00 1
5023 PL 28/12/2012 0:00 28/12/2012 0:00 1
5023 PL 29/12/2012 0:00 29/12/2012 0:00 1
5023 PL 30/12/2012 0:00 30/12/2012 0:00 1
5023 PL 31/12/2012 0:00 31/12/2012 0:00 1
Query result must be as below: -
PAYCODE LV_TYPE FROM_DATE TO_DATE LVALUE
5023 SL 14/12/2012 0:00 15/12/2012 0:00 2
5023 COF 16/12/2012 0:00 16/12/2012 0:00 1
5023 SL 19/12/2012 0:00 19/12/2012 0:00 1
5023 SL 22/12/2012 0:00 24/12/2012 0:00 3
5023 PL 28/12/2012 0:00 31/12/2012 0:00 4
If same type of leave taken continously,it shoul be merged in one row mentioning from_date to to_date.
Please help me.
Thanks,
Anil Kumar
[edit]Code block added - OriginalGriff[/edit]
解决方案
Check this.
SELECT LV_TYPE,LV_TYPE,MIN(FROM_DATE) AS FROM_DATE ,MAX(TO_DATE) AS TO_DATE, COUNT(LVALUEP) AS LVALUE FROM tblLeave GROUP BY PAYCODE,LV_TYPE
Please try following
SELECT PAYCODE,LV_TYPE, MIN(FROM_DATE) AS FROM_DATE, MAX(FROM_DATE) AS TO_DATE, COUNT('A') AS LVALUE FROM ( SELECT PAYCODE,LV_TYPE,FROM_DATE, DATEDIFF(D, ROW_NUMBER() OVER(ORDER BY FROM_DATE), FROM_DATE) AS DtRange FROM tblLeave) AS dt GROUP BY PAYCODE,LV_TYPE,DtRange ORDER BY FROM_DATE
PAYCODE LV_TYPE FROM_DATE TO_DATE LVALUE ----------- ------- ---------- ---------- ----------- 5023 SL 2012-12-14 2012-12-15 2 5023 COF 2012-12-16 2012-12-16 1 5023 SL 2012-12-19 2012-12-19 1 5023 SL 2012-12-22 2012-12-24 3 5023 PL 2012-12-28 2012-12-31 4
I tested Solution 1 and it does not return the result set that you required.
The Stored Procedure below has been tested and returns the result set that you showed in your example. Create this Stored Procedure in your SQL Server database and execute the Stored Procedure in your program. The Stored Procedure will return the rows as shown in your example.
There may be some fancy SQL statement that will return the same result but I wasn''t able to figure it out. I read through my Jim Celko''s SQL for Smarties book and this type of query was not in there. I welcome other contributors that may know SQL better than I to submit a solution that is done in a single SQL statement rather than a Stored Procedure algorithm.
USE [YourDatabaseNameGoesHere] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Mike Meinz -- Create date: 26 January 2013 -- Description: Summarizes Leave Rows -- ============================================= CREATE PROCEDURE SUMMARIZE_LEAVE_ROWS AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @curPayCode int,@curLV_Type varchar(3),@curFrom_Date date,@curTo_Date date,@curLValue int DECLARE @lastPayCode int,@lastLV_Type varchar(3),@lastFrom_Date date,@lastTo_Date date,@lastLValue int DECLARE @SummarizedRowsTable TABLE (PayCode int,LV_Type varchar(3),From_date date,To_Date date,lvalue int) -- -- Select rows from tblLeave Declare Summary_Leave_Cursor CURSOR FOR SELECT PayCode,LV_Type,From_Date,To_Date,lvalue from tblLeave order by PayCode, From_date, LV_Type DECLARE @FIRST_TIME BIT SET @FIRST_TIME=1 OPEN Summary_Leave_Cursor -- Get first row FETCH NEXT FROM Summary_Leave_Cursor INTO @curPayCode,@curLV_Type,@curFrom_date,@curTo_Date,@curLValue WHILE @@FETCH_STATUS = 0 BEGIN IF @FIRST_TIME=1 BEGIN SET @FIRST_TIME=0 -- Save the current row data SET @lastPayCode=@curPayCode SET @lastLV_Type=@curLV_Type SET @lastFrom_Date=@curFrom_Date SET @lastTo_Date=@curTo_Date SET @lastLValue=@curLValue END ELSE BEGIN -- Check if same PayCode and leave type and current FromDate is one day later than last ToDate -- Use DATEDIFF and DATEADD to compare the dates -- If times need to be taken into consideration, then this will have to be changed IF @curPayCode=@lastPayCode AND @curLV_Type=@lastLV_Type AND DATEDIFF(day,@CurFrom_date,DATEADD(day,1,@lastTo_date))=0 BEGIN -- Accumulate the LValue SET @lastlvalue=@lastlvalue + @curlvalue -- Update the ToDate with the new ToDate SET @lastTo_Date=@curTo_Date END ELSE BEGIN -- Insert summarized data into the temporary table INSERT INTO @SummarizedRowsTable (PayCode,LV_Type,From_Date,To_Date,lvalue) Values (@lastPayCode, @lastLV_Type, @lastFrom_Date,@lastTo_Date,@lastLValue); -- Save the current row data SET @lastPayCode=@curPayCode SET @lastLV_Type=@curLV_Type SET @lastFrom_Date=@curFrom_Date SET @lastTo_Date=@curTo_Date SET @lastLValue=@curLValue END END FETCH NEXT FROM Summary_Leave_Cursor INTO @curPayCode,@curLV_Type,@curFrom_date,@curTo_Date,@curLValue; END -- Insert the last summarized row into the temporary table INSERT INTO @SummarizedRowsTable (PayCode,LV_Type,From_Date,To_Date,lvalue) Values (@lastPayCode, @lastLV_Type, @lastFrom_Date,@lastTo_Date,@lastLValue); CLOSE Summary_Leave_Cursor; DEALLOCATE Summary_Leave_Cursor; -- -- Return the temporary table as the stored procedure result SELECT PayCode,LV_Type,From_Date,To_Date,lvalue FROM @SummarizedRowsTable order by PayCode, From_Date, LV_Type; END
This is the table design that I used to test the Stored Procedure:
CREATE TABLE [dbo].[tblLeave]( [PayCode] [int] NOT NULL, [LV_Type] [varchar](3) NOT NULL, [From_date] [date] NOT NULL, [To_Date] [date] NOT NULL, [LValue] [int] NOT NULL ) ON [PRIMARY]
这篇关于SQL Server查询语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
