sql server过滤数据 [英] sql server filtering data

问题描述

productId    productname    enterdate     status
1                lux          1/1/2013      0
2               santoor       2/1/2013      0
3              colgate        2/1/2013      0
4                mouse        2/1/2013      0
null             null         3/1/2013      1
6               keyboard      6/1/2013      0
null             null         7/1/2013      1
8               speakers      8/1/2013      0
9                pen          9/1/2013      0
10               cellphone    10/1/2013     0

我有这样的输出现在我要更新

productid和productname当status = 1



如果在productid中为null，我必须在白天之前获得

值enterdate值

与productname相同



所需的输出是

I have the output like this now i want update the
productid and productname when status =1

when null available in productid i have to get the
value before day enterdate value
same as productname also

The desired output is

productId    productname    enterdate     status
1                lux          1/1/2013      0
2               santoor       2/1/2013      0
3              colgate        2/1/2013      0
4                mouse        2/1/2013      0
4      santoor or colgate or mouse   3/1/2013      1
6               keyboard      6/1/2013      0
6           keyboard        7/1/2013      1
8               speakers      8/1/2013      0
9                pen          9/1/2013      0
10               cellphone    10/1/2013     0

推荐答案

我建​​议你重新设计你的table！

使用 ALTER TABLE [ ^ ]命令将productid更新为不接受空值。



这是一次性解决方案：

I would suggest you to redesign your table!
Use ALTER TABLE[^] command to update productid to not accept null values.

Here is one-usage solution:

DECLARE @tmp TABLE (productId INT NULL, productname VARCHAR(30) NULL, enterdate DATETIME, [status] INT)

SET DATEFORMAT dmy;

INSERT INTO @tmp (productId, productname, enterdate, [status])
VALUES(1, 'lux', '1/1/2013', 0)
INSERT INTO @tmp (productId, productname, enterdate, [status])
VALUES(2, 'santoor', '2/1/2013', 0)
INSERT INTO @tmp (productId, productname, enterdate, [status])
VALUES(3, 'colgate', '2/1/2013', 0)
INSERT INTO @tmp (productId, productname, enterdate, [status])
VALUES(4, 'mouse', '2/1/2013', 0)
INSERT INTO @tmp (productId, productname, enterdate, [status])
VALUES(null, null, '3/1/2013', 1)
INSERT INTO @tmp (productId, productname, enterdate, [status])
VALUES(6, 'keyboard', '6/1/2013', 0)
INSERT INTO @tmp (productId, productname, enterdate, [status])
VALUES(null, null, '7/1/2013', 1)
INSERT INTO @tmp (productId, productname, enterdate, [status])
VALUES(8, 'speakers', '8/1/2013', 0)
INSERT INTO @tmp (productId, productname, enterdate, [status])
VALUES(9, 'pen', '9/1/2013', 0)
INSERT INTO @tmp (productId, productname, enterdate, [status])
VALUES(10, 'cellphone', '10/1/2013', 0)

--insert products with <code>status=1</code> and <code>enterdate=previous date</code> 
INSERT INTO @tmp (productId, productname, enterdate, [status])
SELECT t1.productid, t1.productname, t2.enterdate, t2.status
FROM @tmp AS t1 INNER JOIN (
		SELECT enterdate, [status]
		FROM @tmp
		WHERE productId IS NULL AND productname IS NULL
	) AS t2 ON t1.enterdate = DATEADD(dd,-1,t2.enterdate)
WHERE t1.productId IS NOT NULL AND t1.productname IS NOT NULL

--delete nulls!!!
DELETE 
FROM @tmp
WHERE productId IS NULL AND productname IS NULL

--display "updated" values
SELECT *
FROM @tmp
ORDER BY enterdate, productid

结果：

Result:

1   lux          2013-01-01 00:00:00.000     0
2   santoor      2013-01-02 00:00:00.000     0
3   colgate      2013-01-02 00:00:00.000     0
4   mouse        2013-01-02 00:00:00.000     0
2   santoor      2013-01-03 00:00:00.000     1
3   colgate      2013-01-03 00:00:00.000     1
4   mouse        2013-01-03 00:00:00.000     1
6   keyboard     2013-01-06 00:00:00.000     0
6   keyboard     2013-01-07 00:00:00.000     1
8   speakers     2013-01-08 00:00:00.000     0
9   pen          2013-01-09 00:00:00.000     0
10  cellphone    2013-01-10 00:00:00.000     0

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