大致是这样了:
select Year,Min(Cns)
from (
select Year,Value,Count(1) as Cns
from Coins
where Year in (
select year
from (
SELECT distinct year,value from Coins a where value in(1,2,5)
) t group by year having count(*)=3
)
group by Year,Value
) tx
group by Year
SELECT YEAR,COUNT(*) FROM (
select year from (
SELECT distinct year,Parvalue from tth1 a where Parvalue in(1,2,5)) group by year having count(*)=3)
group by year
SELECT YEAR,COUNT(*) FROM (
select year from (
SELECT distinct year,Parvalue from tth1 a where Parvalue in(1,2,5)) group by year having count(*)=3)
group by year
[Quote=引用 4 楼 wwwwa 的回复:]
or
select year from (
SELECT distinct year,Parvalue from tth1 a where Parvalue in(1,2,5)) group by year having count(*)=3
[/Quote]
这个解答了哪一年至少够一套,但是多少套仍不知道啊,因为有的年份可能可以配出10套100套呢?
[Quote=引用 4 楼 wwwwa 的回复:]
or
select year from (
SELECT distinct year,Parvalue from tth1 a where Parvalue in(1,2,5)) group by year having count(*)=3
[/Quote]
这样做似乎解决了“哪些年份能配出至少一套来”,但是没有得出总共有多少套的问题,有的年份可以凑出不1套啊?
select yaer,count(*) from (
SELECT distinct year from tth1 a where exists(select 1 from tth1 where Year=a.Year and Parvalue=1)
and
exists(select 1 from tth1 where Year=a.Year and Parvalue=2)
and
exists(select 1 from tth1 where Year=a.Year and Parvalue=5)) group by year