sql server过滤数据 [英] sql server filter data
本文介绍了sql server过滤数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
大家好
我有这样的数据
姓名日期Holidayname
mohammed.shareef 6/1/2013周末
mohammed.shareef 6/2/2013周末
mohammed.shareef 2013年6月3日
mohammed.shareef 6/3/2013假日
mohammed.shareef 6/4/2013
mohammed.shareef 6/5/2013
nagarjuna.doddipatla 2013年6月1日周末
nagarjuna.doddipatla 6/2/2013周末
nagarjuna.doddipatla 6/3/2013假日
nagarjuna.doddipatla 2013年6月4日
nagarjuna.doddipatla 2013年6月5日
我希望得到结果
姓名日期Holidayname
mohammed.shareef 6/1/2013周末
mohammed.shareef 6/2/2013周末
mohammed.shareef 6/3/2013假日
mohammed.shareef 6/4/2013
mohammed.shareef 2013年6月5日
nagarjuna.doddipatla 2013年6月1日周末
nagarjuna.doddipatla 6/2/2013周末
nagarjuna.doddipatla 6/3/2013假日
nagarjuna.doddipa tla 6/4/2013
nagarjuna.doddipatla 2013年6月5日
任何人都可以帮助我。
Prakash.ch
解决方案
选择名称, date ,Holidayname 来自
(
选择 ROW_NUMBER() over ( partition by name, date order by name, date ,len(Holidayname) desc )
as srno,* 来自 TBL_Nm
) as a
其中 srno = 1
Happy Coding!
:)
试试这个:
DECLARE @tmp 表([名称] VARCHAR ( 30 ),[日期] DATETIME ,Holidayname VARCHAR ( 30 ) NULL )
INSERT INTO @ tmp ([名称],[ 日期],[Holidayname])
SELECT ' mohammed.shareef' AS [Name],' 6/1/2013' AS [日期],' 周末' AS [Holidayname]
UNION ALL SELECT ' mohammed.shareef',' 2013年6月2日',' 周末'
UNION ALL SELECT ' mohammed.shareef',' 6/3/2013', NULL
UNION ALL SELECT ' mohammed.shareef',' 2013年6月3日', ' Holiday'
UNION ALL SELECT ' mohammed.shareef',' 6/4/2013', NULL
UNION ALL < span class =code-keyword> SELECT ' mohammed.shareef',' 6/5/2013', NULL
UNION ALL SELECT ' nagarjuna.doddipatla',' 6/1 / 2013',' 周末'
UNION ALL SELECT ' nagarjuna.doddipatla',' 6/2 / 2013',' 周末'
UNION ALL SELECT ' nagarjuna.doddipatla',' 6/3 / 2013',' Holiday'
UNION ALL SELECT ' nagarjuna.doddipatla',' 6/4/2013', NULL
UNION ALL SELECT ' nagarjuna.doddipatla',' 6/5/2013', NULL
SELECT DISTINCT t1。[名称],t1。[日期],( SELECT [HolidayName] FROM @ tmp WHERE [Name] = t1。[Name] AND [日期] = t1。[日期] AND [HolidayName] IS NOT NULL )< span class =code-keyword> AS [HolidayName]
FROM @ tmp AS t1
ORDER BY [Name],[ Date ]
警告!这不是最佳解决方案(数千个数据的执行时间很长)。
你可以尝试下面的SQL查询
SELECT * FROM (
SELECT
NAME, DATE 跨度> ,HOLIDAYNAME,ROW_NUMBER() OVER ( PARTITION BY NAME, DATE ORDER BY NAME , DATE ,HOLIDAYNAME) AS ROW
FROM
TABLENAME)TBL
WHERE TBL.ROW = 1
Hi all
I have the data like this
Name Date Holidayname
mohammed.shareef 6/1/2013 Weekend
mohammed.shareef 6/2/2013 Weekend
mohammed.shareef 6/3/2013
mohammed.shareef 6/3/2013 Holiday
mohammed.shareef 6/4/2013
mohammed.shareef 6/5/2013
nagarjuna.doddipatla 6/1/2013 Weekend
nagarjuna.doddipatla 6/2/2013 Weekend
nagarjuna.doddipatla 6/3/2013 Holiday
nagarjuna.doddipatla 6/4/2013
nagarjuna.doddipatla 6/5/2013
I want to get result as
Name Date Holidayname
mohammed.shareef 6/1/2013 Weekend
mohammed.shareef 6/2/2013 Weekend
mohammed.shareef 6/3/2013 Holiday
mohammed.shareef 6/4/2013
mohammed.shareef 6/5/2013
nagarjuna.doddipatla 6/1/2013 Weekend
nagarjuna.doddipatla 6/2/2013 Weekend
nagarjuna.doddipatla 6/3/2013 Holiday
nagarjuna.doddipatla 6/4/2013
nagarjuna.doddipatla 6/5/2013
could any one help me out.
Prakash.ch
解决方案
select name,date,Holidayname from ( select ROW_NUMBER() over(partition by name, date order by name,date,len(Holidayname) desc) as srno,* from TBL_Nm ) as a where srno=1
Happy Coding!
:)
Try this:
DECLARE @tmp TABLE([Name] VARCHAR(30), [Date] DATETIME, Holidayname VARCHAR(30) NULL) INSERT INTO @tmp ([Name], [Date], [Holidayname]) SELECT 'mohammed.shareef' AS [Name], '6/1/2013' AS [Date], 'Weekend' AS [Holidayname] UNION ALL SELECT 'mohammed.shareef', '6/2/2013', 'Weekend' UNION ALL SELECT 'mohammed.shareef', '6/3/2013', NULL UNION ALL SELECT 'mohammed.shareef', '6/3/2013', 'Holiday' UNION ALL SELECT 'mohammed.shareef', '6/4/2013', NULL UNION ALL SELECT 'mohammed.shareef', '6/5/2013', NULL UNION ALL SELECT 'nagarjuna.doddipatla', '6/1/2013', 'Weekend' UNION ALL SELECT 'nagarjuna.doddipatla', '6/2/2013', 'Weekend' UNION ALL SELECT 'nagarjuna.doddipatla', '6/3/2013', 'Holiday' UNION ALL SELECT 'nagarjuna.doddipatla', '6/4/2013', NULL UNION ALL SELECT 'nagarjuna.doddipatla', '6/5/2013', NULL SELECT DISTINCT t1.[Name], t1.[Date], (SELECT [HolidayName] FROM @tmp WHERE [Name] = t1.[Name] AND [Date] = t1.[Date] AND [HolidayName] IS NOT NULL) AS [HolidayName] FROM @tmp AS t1 ORDER BY [Name], [Date]
Warning! This is not optimal solution (time of execution would be long for thousands of data).
Hi,
You can try below SQL Query
SELECT * FROM( SELECT NAME,DATE,HOLIDAYNAME,ROW_NUMBER() OVER (PARTITION BY NAME,DATE ORDER BY NAME,DATE,HOLIDAYNAME ) AS ROW FROM TABLENAME) TBL WHERE TBL.ROW = 1
这篇关于sql server过滤数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文