需要T-SQL查询找到所有可能的方法 [英] Need T-SQL Query find all possible ways
本文介绍了需要T-SQL查询找到所有可能的方法的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
create table #sample (
product varchar(100),
Price float
)
insert into #sample values ('Pen',10)
insert into #sample values ('DVD',29)
insert into #sample values ('Pendrive',45)
insert into #sample values ('Mouse',12.5)
insert into #sample values ('TV',49)
select * from #sample
考虑这种情况...
我有1000美元,我想购买上面列出的东西.
I have 1000$, I want to buy something listed above.
我想花掉全部
所以我需要一个查询,该查询将给出所有产品中多少单位的价格为1000美元
So I need a query which gives how much units in all products will cost 1000$
有什么帮助吗?
推荐答案
这是硬编码的,几乎没有灵活性.我的系统花了2分钟才能运行.但是可能会有所帮助,如果没有,抱歉. fnGenerate_Numbers是一个表函数,它返回参数范围内的整数. 做到这一点的方法. >
This is hard coded and has little flexiblity. Took my system 2 minutes to run. But might be helpful, sorry if it isn't. fnGenerate_Numbers is a table function that returns integers within the range of the parameters. Ways to do that.
DECLARE @Max INT,
@Pens money,
@Dvds money,
@Pendrives money,
@Mouses money,
@Tvs money
SELECT @Max = 1000,
@Pens = 10,
@Dvds = 29,
@Pendrives = 45,
@Mouses = 12.5,
@Tvs = 49
;WITH Results AS
(
SELECT p.n pens, d.n dvds, pd.n pendrives, m.n mouses, t.n tvs, tot.cost
FROM fnGenerate_Numbers(0, @Max/@Pens) p -- Pens
CROSS JOIN fnGenerate_Numbers(0, @Max/@Dvds) d -- DVDs
CROSS JOIN fnGenerate_Numbers(0, @Max/@Pendrives) pd -- Pendrives
CROSS JOIN fnGenerate_Numbers(0, @Max/@Mouses) m -- Mouses
CROSS JOIN fnGenerate_Numbers(0, @Max/@Tvs) t -- Tvs
CROSS APPLY (SELECT p.n * @Pens + d.n * @Dvds + pd.n * + @Pendrives + m.n * @Mouses + t.n * @Tvs cost) tot
WHERE tot.cost < @Max
), MaxResults AS
(
SELECT
MAX(pens) pens,
dvds,
pendrives,
mouses,
tvs
FROM Results
GROUP BY
dvds,
pendrives,
mouses,
tvs
)
SELECT mr.*, r.cost FROM MaxResults mr
INNER JOIN Results r ON mr.pens = r.pens
AND mr.dvds = r.dvds
AND mr.pendrives = r.pendrives
AND mr.mouses = r.mouses
AND mr.tvs = r.tvs
ORDER BY cost
这篇关于需要T-SQL查询找到所有可能的方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文