sql-server - 使用未在 mssql 中运行的变量选择查询

Question

在 MSSQL2014 中使用变量时选择查询不起作用我的架构是:-

    CREATE TABLE product 
    (idproduct int, name varchar(50), description varchar(50), tax decimal(18,0))


INSERT INTO product
    (idproduct, name, description,tax)
VALUES
    (1, 'abc', 'This is abc',10),
    (2, 'xyz', 'This is xyz',20),
    (3, 'pqr', 'This is pqr',15)


CREATE TABLE product_storage 
    (idstorage int,idproduct int,added datetime, quantity int, price decimal(18,0))


INSERT INTO product_storage 
    (idstorage,idproduct, added, quantity,price)
VALUES
    (1, 1, 2010-01-01,0,10.0),
    (2, 1, 2010-01-02,0,11.0),
    (3, 1, 2010-01-03,10,12.0),
    (4, 2, 2010-01-04,0,12.0),
    (5, 2, 2010-01-05,10,11.0),
(6, 2, 2010-01-06,10,13.0),
(7, 3, 2010-01-07,10,14.0),
(8, 3, 2010-01-07,10,16.0),
(9, 3, 2010-01-09,10,13.0)

我正在执行以下命令:-

declare @price1 varchar(10)


SELECT p.idproduct, p.name, p.tax,
[@price1]=(SELECT top 1 s.price
        FROM product_storage s
        WHERE s.idproduct=p.idproduct AND s.quantity > 0
        ORDER BY s.added ASC),
 (@price1 * (1 + tax/100)) AS [price_with_tax]
FROM product p

;

这在 MSSQL 中不起作用，请帮助我。详细查看http://sqlfiddle.com/#!6/91ec2/296

我的查询正在 MYSQL 中运行检查详细信息:- http://sqlfiddle.com/#!9/a71b8/1

Answer 1

尝试这个查询

SELECT 
    p.idproduct
    , p.name
    , p.tax
    , (t1.price * (1 + tax/100)) AS [price_with_tax]
FROM product p
inner join 
(
    SELECT ROW_NUMBER() over (PARTITION by s.idproduct order by s.added ASC) as linha, s.idproduct, s.price 
    FROM product_storage s
    WHERE s.quantity > 0    
) as t1
    on t1.idproduct = p.idproduct and t1.linha = 1