我需要在同一列中添加一个计算列项。请查看我的SQL代码,包括现有数据和期望的结果。
产品O按照以下方式添加: - 对于201501,产品O=产品Y、W、N的
谢谢。 Helal
SQL:
产品O按照以下方式添加: - 对于201501,产品O=产品Y、W、N的
en_count
之和,当yrmnth = 201501
时。
- 对于201502,产品O=产品Y、W、N的en_count
总和,当yrmnth = 20150
时。谢谢。 Helal
SQL:
--Existing Data
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Table1') IS NOT NULL DROP TABLE #Table1
--===== Create the test table with
CREATE TABLE #Table1
(
product char(100),
yrmnth varchar(6),
en_count int,
date date,
)
INSERT INTO #Table1 (product, yrmnth, en_count, date)
SELECT 'Y', '201501', 5000 , '01/01/2015' union all
SELECT 'Y', '201502', 6000 , '02/01/2015' union all
SELECT 'Z', '201501', 7000 , '01/01/2015' union all
SELECT 'Z', '201502', 8000 , '02/01/2015' union all
SELECT 'W', '201501', 9000 , '01/01/2015' union all
SELECT 'W', '201502', 10000 , '02/01/2015' union all
SELECT 'N', '201501', 11000 , '01/01/2015' union all
SELECT 'N', '201502', 12000 , '02/01/2015'
--Desired Outcome
IF OBJECT_ID('TempDB..#Table2') IS NOT NULL DROP TABLE #Table2
--===== Create the test table with
CREATE TABLE #Table2
(
product char(100),
yrmnth varchar(6),
en_count int,
date date,
)
INSERT INTO #Table2 (product, yrmnth, en_count, date)
SELECT 'Y', '201501', 5000 , '01/01/2015' union all
SELECT 'Y', '201502', 6000 , '02/01/2015' union all
SELECT 'Z', '201501', 7000 , '01/01/2015' union all
SELECT 'Z', '201502', 8000 , '02/01/2015' union all
SELECT 'W', '201501', 9000 , '01/01/2015' union all
SELECT 'W', '201502', 10000 , '02/01/2015' union all
SELECT 'N', '201501', 11000 , '01/01/2015' union all
SELECT 'N', '201502', 12000 , '02/01/2015' union all
SELECT 'O', '201501', 32000 , '01/01/2015' union all
SELECT 'O', '201502', 36000 , '02/01/2015'
select *
from #Table2