22,207
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([Date] Date,City nvarchar(22),Stock nvarchar(21),[ID] int)
Insert #T
select '2018-10-01',N'北京',N'有',123 union all
select '2018-10-01',N'上海',N'有',123 union all
select '2018-10-02',N'北京',N'有',123 union all
select '2018-10-02',N'上海',N'无',123 union all
select '2018-10-01',N'北京',N'有',1234 union all
select '2018-10-01',N'上海',N'有',1234 union all
select '2018-10-02',N'北京',N'有',1234 union all
select '2018-10-02',N'上海',N'无',1234
Go
--测试数据结束
SELECT
City,
Date,
Stock
FROM
master..spt_values
LEFT JOIN
#T
ON DATEADD(DAY, number, '2018-10-01') = Date
AND ID = '1234' --添加id条件这里
WHERE
type = 'P'
AND number
BETWEEN 0 AND DATEDIFF(DAY, '2018-10-01', '2018-10-10')
ORDER BY
CASE City
WHEN '北京'
THEN '0'
WHEN '上海'
THEN '1'
WHEN '广州'
THEN '2'
WHEN '成都'
THEN '3'
END,
Date;
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T(Date Date,City nvarchar(22),Stock nvarchar(21))
Insert #T
select '2018-10-01',N'北京',N'有' union all
select '2018-10-01',N'上海',N'有' union all
select '2018-10-02',N'北京',N'有' union all
select '2018-10-02',N'上海',N'无'
Go
--测试数据结束
SELECT
City,
Date,
Stock
FROM
master..spt_values
LEFT JOIN
#T
ON DATEADD(DAY, number, '2018-10-01') = Date
WHERE
type = 'P'
AND number
BETWEEN 0 AND DATEDIFF(DAY, '2018-10-01', '2018-10-10')
ORDER BY
CASE City
WHEN '北京'
THEN '0'
WHEN '上海'
THEN '1'
WHEN '广州'
THEN '2'
WHEN '成都'
THEN '3'
END,
Date;
SELECT
City,
Date,
Stock
FROM
master..spt_values
LEFT JOIN OnSaleStock ON DATEADD(DAY, number, '2018-10-01') = Date
WHERE
ID = '1234567'
AND type = 'P'
AND number
BETWEEN 0 AND DATEDIFF(DAY, '2018-10-01', '2018-10-10')
ORDER BY
CASE City
WHEN '北京'
THEN '0'
WHEN '上海'
THEN '1'
WHEN '广州'
THEN '2'
WHEN '成都'
THEN '3'
END,
Date;
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([日期] Date,[地区] nvarchar(22),[库存] nvarchar(21))
Insert #T
select '2018-10-01',N'北京',N'有' union all
select '2018-10-01',N'上海',N'有' union all
select '2018-10-02',N'北京',N'有' union all
select '2018-10-02',N'上海',N'无'
Go
--测试数据结束
SELECT
CONVERT(VARCHAR(10),DATEADD(DAY, number, '2018-10-01'),120) AS 日期,地区,库存
FROM
master..spt_values
LEFT JOIN
#T
ON DATEADD(DAY, number, '2018-10-01') = [日期]
WHERE
type = 'P'
AND number
BETWEEN 0 AND DATEDIFF(DAY, '2018-10-01', '2018-10-05') ;