m*********a 发帖数: 3299 | 1 If there is database have recipeid, and its component list
id, component
1, 2
1, 3
2, 1
2, 2
2, 4
3, 1
3, 2
How do I know the recipes with id 1,2,4 belongs to recipe id 2? |
m******u 发帖数: 12400 | |
m*********a 发帖数: 3299 | 3 i solve this with two steps
1. using intersect to find recipes contains all components searched
2. Looking through the result from step 1
Count the component number, the target recipe should have equal number of
components.
Anyone has a better way.
【在 m******u 的大作中提到】 : wow,这么男的题。
|
a******t 发帖数: 125 | 4 First, find all recipe IDs that contains component 1;
Second, among the set returned from the first step, find all recipes that
contains component 2. Now you get all recipe IDs that have both component 1
and 2;
And so on...
★ 发自iPhone App: ChineseWeb 8.7
【在 m*********a 的大作中提到】 : i solve this with two steps : 1. using intersect to find recipes contains all components searched : 2. Looking through the result from step 1 : Count the component number, the target recipe should have equal number of : components. : Anyone has a better way.
|
s**********o 发帖数: 14359 | 5 请用中文举个例子好吗
菜名ID 成分
红烧茄子 茄子
红烧茄子 蒜
红烧茄子 葱
冬瓜火腿汤 冬瓜
冬瓜火腿汤 火腿
冬瓜火腿汤 冰糖
你是问怎么知道冬瓜,火腿,冰糖属于冬瓜火腿汤呢
直接SELECT 成分 WHERE 菜名ID=冬瓜火腿汤
不就可以了 |
x****e 发帖数: 1773 | 6 没看懂?
“recipes with id 1,2,4”?
【在 m*********a 的大作中提到】 : i solve this with two steps : 1. using intersect to find recipes contains all components searched : 2. Looking through the result from step 1 : Count the component number, the target recipe should have equal number of : components. : Anyone has a better way.
|
m*********a 发帖数: 3299 | 7 写错了是componet id 1,2, 4
【在 x****e 的大作中提到】 : 没看懂? : “recipes with id 1,2,4”?
|
m*********a 发帖数: 3299 | 8 关键是有很多recipe,不知道哪个有这个成分
比如有1百万个
你老一个个看过去么?
【在 s**********o 的大作中提到】 : 请用中文举个例子好吗 : 菜名ID 成分 : 红烧茄子 茄子 : 红烧茄子 蒜 : 红烧茄子 葱 : 冬瓜火腿汤 冬瓜 : 冬瓜火腿汤 火腿 : 冬瓜火腿汤 冰糖 : 你是问怎么知道冬瓜,火腿,冰糖属于冬瓜火腿汤呢 : 直接SELECT 成分 WHERE 菜名ID=冬瓜火腿汤
|
s**********o 发帖数: 14359 | 9 不就是个SELECT DISTINCT 菜名ID FROM TABLE WHERE 成分=‘葱’
数据库的基本概念你都不怎么明确,数据库基本是SET的操作,一个一个
的找那是CURSOR很慢的
【在 m*********a 的大作中提到】 : 关键是有很多recipe,不知道哪个有这个成分 : 比如有1百万个 : 你老一个个看过去么?
|
n****f 发帖数: 905 | 10 How do I know the recipes with components 1,2,4 belongs to recipe id 2?
1. recipes with components 1,2,4
SELECT * FROM THE_TABLE_NAME WHERE COMPONENT IN (1,2,4)
2. recipe id 2
SELECT * FROM THE_TABLE_NAME WHERE ID = 2
3. SO:
SELECT * FROM THE_TABLE_NAME WHERE ID = 2 AND COMPONENT IN (1,2,4)
IS THIS OK FOR U?
【在 m*********a 的大作中提到】 : 关键是有很多recipe,不知道哪个有这个成分 : 比如有1百万个 : 你老一个个看过去么?
|
|
|
s**********o 发帖数: 14359 | 11 还是写个STORED PROCEDURE吧
选出有COMP=1的RECEIPE
选出有COMP=2的RECEIPE
选出有COMP=3的RECEIPE
取个交集就行了
如果RECEPE 1不在这个TABLE里就NO,否则YES |
m*********a 发帖数: 3299 | 12 这个肯定是错的
几乎所有的recipe 都选了
【在 n****f 的大作中提到】 : How do I know the recipes with components 1,2,4 belongs to recipe id 2? : 1. recipes with components 1,2,4 : SELECT * FROM THE_TABLE_NAME WHERE COMPONENT IN (1,2,4) : 2. recipe id 2 : SELECT * FROM THE_TABLE_NAME WHERE ID = 2 : 3. SO: : SELECT * FROM THE_TABLE_NAME WHERE ID = 2 AND COMPONENT IN (1,2,4) : IS THIS OK FOR U?
|
m*********a 发帖数: 3299 | 13 我现在是这么做的
但是我在想是不是这样也行
用self join
如果有三个成分table = recipes
select *
from recipe r1
join recipe r2 on r1.id = r2.id
join recipe r3 on r1.id = r3.id
where
r1.componet = compnent1
and r2.component = component2
and r3.component = component3
如果是4个的就join 四次
反正用loop 产生from clause 和where clause
【在 s**********o 的大作中提到】 : 还是写个STORED PROCEDURE吧 : 选出有COMP=1的RECEIPE : 选出有COMP=2的RECEIPE : 选出有COMP=3的RECEIPE : 取个交集就行了 : 如果RECEPE 1不在这个TABLE里就NO,否则YES
|
s**********o 发帖数: 14359 | 14 其实这样写STORED PROC比较好,我搞个SEUDO CODE,怎么写CODE你自己研究
进来的参数是recepeid=1,comp=1,2,4
SET @RETURN ='YES'
select distinct recepeid from table where comp = 1
如果recepeid=1不在TABLE里, SET @RETURN ='NO' 结束
select distinct recepeid from table where comp = 2
如果recepeid=1不在TABLE里,SET @RETURN ='NO' 结束
select distinct recepeid from table where comp = 4
如果recepeid=1不在TABLE里,SET @RETURN ='NO' 结束
结束:RETURN @RETURN
当然了你把COMP ID搞成一个LIST,然后LOOP THROUGH比较好
你现在是3个,SELFJOIN 3次,你要是30个,300个,这个数字
是不定的,只能去LOOP
【在 m*********a 的大作中提到】 : 我现在是这么做的 : 但是我在想是不是这样也行 : 用self join : 如果有三个成分table = recipes : select * : from recipe r1 : join recipe r2 on r1.id = r2.id : join recipe r3 on r1.id = r3.id : where : r1.componet = compnent1
|
B*****g 发帖数: 34098 | 15 其实没看懂题.....
SELECT r.id
FROM recipes r
WHERE r.component IN (1,2,4)
GROUP BY r.id
HAVING COUNT(DISTINCT r.component) = 3
【在 m*********a 的大作中提到】 : 我现在是这么做的 : 但是我在想是不是这样也行 : 用self join : 如果有三个成分table = recipes : select * : from recipe r1 : join recipe r2 on r1.id = r2.id : join recipe r3 on r1.id = r3.id : where : r1.componet = compnent1
|
s**********o 发帖数: 14359 | 16 其实这个问题我一开始也看错了,问的应该是,给你已知的COMPONENT LIST和RECEPE
LIST
告诉我哪些RECEPE有所有的COMPONENT
【在 B*****g 的大作中提到】 : 其实没看懂题..... : SELECT r.id : FROM recipes r : WHERE r.component IN (1,2,4) : GROUP BY r.id : HAVING COUNT(DISTINCT r.component) = 3
|
n***l 发帖数: 143 | 17 Google 了一下,For XML Path is able to concatenate columns in a group by
clause.
Here is the result:
Recipe Components
1 2, 3
2 1, 2, 4
3 1, 2
Here is the link:
http://stackoverflow.com/questions/273238/how-to-use-group-by-t
The adapted script:
SELECT
[ID] as [Recipe],
STUFF((
SELECT ', ' + CAST([component] AS VARCHAR(MAX))
FROM recipe
WHERE (ID = Results.ID)
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
,1,2,'') AS [Components]
FROM recipe Results
GROUP BY ID |
s**********o 发帖数: 14359 | 18 其实这个就更复杂了,这个RELATIONAL DATABASE可能不好搞定
Recipe Components
酱油 大豆
酱油 盐
粉条 绿豆
红烧肉 酱油
红烧肉 猪肉
红烧肉 盐
猪肉粉条 红烧肉
猪肉粉条 粉条
大杂烩 猪肉粉条
大杂烩 红烧肉
大杂烩 酱油
大杂烩 醋
大杂烩 盐 |