SSMS和SQL Server:如何结合SELECT AS和SELECT =

Question

当供应商(或我们)迟到且有效价格日期已过时，此选项用于将价格归零。我正在使用的完整代码是:

DECLARE @Today nvarchar(20) = CONCAT(SUBSTRING(CONVERT(nvarchar, GETDATE(), 101), 7, 4), '.', SUBSTRING(CONVERT(nvarchar, GETDATE(), 101), 1, 2), SUBSTRING(CONVERT(nvarchar, GETDATE(), 101), 4, 2))
SELECT --l.location_id
im.item_id AS [Item ID]
,price1 = 0000.0000
,price2 = 0000.0000
,price3 = @Today
,im.price4
,im.price5
,price10 = @Today
,standard_cost = 0000.0000
,list_price = 0000.0000
,cost = 0000.0000
FROM    p21_view_inv_mast im
JOIN    p21_view_inv_loc l ON l.inv_mast_uid = im.inv_mast_uid 
AND l.location_id = 1 
JOIN    p21_view_inventory_supplier s ON s.supplier_id = l.primary_supplier_id 
AND s.inv_mast_uid = im.inv_mast_uid
WHERE   l.primary_supplier_id = 100592  --Suplier ID, change per supplier
AND im.delete_flag <> 'Y'
AND ISNULL(l.delete_flag, 'N') <> 'Y'

我希望代码完全相同，只是更改了price1(为"Price 1")、price2(为"Price 2")和price3(为"Price 3")的列标题。

Answer 1

这就是你想要做的吗?

SELECT 0000.0000 as [Price 1]

Answer 2

如果您正在处理表中的数据，我假设您在这里，那么您需要一个UPDATE来设置值，然后再读取它。

Option 1 - UPDATE/SELECT(2语句)

UPDATE Table1 SET price1 = 0000.0000;
SELECT price1 as [Price 1] FROM Table1;

选项2 - UPDATE/OUTPUT(单语句)

UPDATE Table1
SET price1 = 0000.0000
OUTPUT Inserted.price1 as [Price 1];

假设price1实际上来自一个名为Table1的表，因为您的示例没有提供该上下文。

还请注意，这并不包括您在实际情况中最有可能需要的WHERE子句。

编辑如果你想隐藏你的数据和修改你的列标题，那么你想使用列别名，就像每个人在评论中告诉你的那样:

SELECT 0000.0000 as [Price 1],
0000.0000 as [Price 2],
[etc...]
FROM ....
WHERE ...

在你的例子中:

declare @Today nvarchar(20) = concat(substring(convert(nvarchar,getdate(),101),7,4),'.',substring(convert(nvarchar,getdate(),101),1,2),substring(convert(nvarchar,getdate(),101),4,2))
select  --l.location_id
im.item_id as [Item ID]
,0000.0000 AS [Price 1]
,0000.0000 AS [Price 2]
,price3 = @Today
,0000.0000 as [Price 4]
,0000.0000 as [Price 5
,price10 = @Today
,0000.0000 AS [Standard Cost]
,0000.0000 = AS [List Price]
,0000.0000 = AS [Cost]

from    p21_view_inv_mast im
join p21_view_inv_loc l on l.inv_mast_uid = im.inv_mast_uid and l.location_id = 1 
join p21_view_inventory_supplier s on s.supplier_id = l.primary_supplier_id and s.inv_mast_uid = im.inv_mast_uid

where   l.primary_supplier_id = 100592  --Suplier ID, change per supplier
and im.delete_flag <> 'Y'
and ISNULL(l.delete_flag, 'N') <> 'Y'