在sql中选择数据的问题 [英] Problem with Selecting datas in sql

问题描述

我的一张桌子有以下字段

预订房间

Hi,

One of my table has following fields

roombooked

rid roomno  cusid  arrdate     deptdate    status
1    102     3     10/13/2012  10/17/2012   grnt
2    103     5     10/14/2012  10/19/2012   grnt
3    105     1     10/11/2012  10/12/2012   grnt

如果我从迄今提供，它会给例如预订的房间编号

如果我给2012年10月16日和2012年10月19日显示
客房号102,103

If i give from date todate, it gives roomno which is booked for example

If i give 10/16/2012 and 19/10/2012 it displays
roomno 102,103

How to do it?

推荐答案

将它们作为表格返回很容易:

Returning them as a table is easy:

SELECT roomno FROM myTable WHERE deptdate BETWEEN '2012-10-16' AND '2012-10-19'

会做到的.
由于逗号分隔的值更难:

will do that.
As comma separated values is harder:

SELECT SUBSTRING(
   (SELECT ',' + CONVERT(varchar, roomno) FROM myTable WHERE deptdate BETWEEN '2012-10-16' AND '2012-10-19'
   FOR XML PATH('')),2,100000) AS CSV

应该这样做.

[edit]更正的CONVERT函数调用-OriginalGriff [/edit]

should do it.

[edit]Corrected CONVERT function call - OriginalGriff[/edit]

尝试以下t-sql代码块:

try following t-sql code block :

CREATE TABLE #TEMP
(
	RID		INT,
	ROOMNO		INT, 
	CUSID		INT,  
	ARRDATE		DATETIME,
	DEPTDATE        DATETIME,
	STATUS		VARCHAR(50)
)

INSERT INTO #TEMP VALUES(1,102,3,'10/13/2012','10/17/2012','GRNT')
INSERT INTO #TEMP VALUES(2,103,5,'10/14/2012','10/19/2012','GRNT')
INSERT INTO #TEMP VALUES(3,105,1,'10/11/2012','10/12/2012','GRNT')

DECLARE @RoomNo  VARCHAR(max)

SELECT	@RoomNo= ISNULL(@RoomNo + ',', '') + cast(ROOMNO as varchar(5))  
FROM	#TEMP
WHERE	(ARRDATE BETWEEN '10-16-2012' AND '10-19-2012'
		OR	DEPTDATE BETWEEN '10-16-2012' AND '10-19-2012')
		
SELECT	@RoomNo

DROP TABLE #TEMP

它会为您工作.

it will work for you.

这篇关于在sql中选择数据的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！