我是MySQL和PHP新手。
我有两张这样的桌子,有上千行。
Table "Company A"
id | Date | Prize_1 | Prize_2 | Prize_3 | Prize_Consolation
1 | 2011-01-13 | "car" | "ipad2" | "bag" | "mouse"
2 | 2011-02-13 | "ps3" | "iphone4" | "mouse" | "bag"
Table "Company B"
id | Date | Prize_1 | Prize_2 | Prize_3 | Prize_Consolation
1 | 2011-01-18 | "tv" | "iphone4" | "ipod" | "bag"
2 | 2011-02-13 | "iphone4" | "ipad" | "bag" | "mouse"
用户将随机发送查询以搜索“奖品”的历史信息,奖品的发放时间和公司。
示例:我想在两个表上搜索奖品“iphone4”,它应该返回如下结果:
Company | Date | Prize
Company A | 2011-02-13 | Prize_2
Company B | 2011-01-18 | Prize_2
Company B | 2011-02-13 | Prize_1
Total 3 prizes for "iphone4"
什么MySQL查询可以实现这种查找搜索?
最佳答案
这些表实际上不应该是两个单独的表,而是一个带有公司标识列的表。
不过,你的情况可以用一种老套的方式解决:
SELECT
'Company A' AS Company,
Date,
CASE
WHEN Prize_1 = 'iphone4' THEN 'Prize_1'
WHEN Prize_2 = 'iphone4' THEN 'Prize_2'
WHEN Prize_3 = 'iphone4' THEN 'Prize_3'
WHEN Prize_Consolation = 'iphone4' THEN 'Prize_Consolation'
ELSE NULL
END AS Prize
FROM `Company A`
WHERE
Prize_1 = 'iphone4'
OR Prize_2 = 'iphone4'
OR Prize_3 = 'iphone4'
OR Prize_Consolation = 'iphone4'
/* UNION performs the same query against both tables and combine the results */
UNION
SELECT
'Company B' AS Company,
Date,
CASE
WHEN Prize_1 = 'iphone4' THEN 'Prize_1'
WHEN Prize_2 = 'iphone4' THEN 'Prize_2'
WHEN Prize_3 = 'iphone4' THEN 'Prize_3'
WHEN Prize_Consolation = 'iphone4' THEN 'Prize_Consolation'
ELSE NULL
END AS Prize
从
Company B
其中
Prize_1='iphone4'
或Prize_2='iphone4'
或Prize_3='iphone4'
或Prize_安慰='iphone4'
这种设计更合适的表格布局如下:
Table Prizes
---------------
id INT AUTO_INCREMENT,
Date DATETIME,
Company VARCHAR(256),
Prize_Type INT,
Prize_Item VARCHAR(256)
Table Prize_Types
---------------
Prize_Type INT AUTO_INCREMENT PRIMARY KEY,
Prize_Description VARCHAR(32)
奖品
1 | 2012-01-28 08:00:00 | 'Company A' | 3 | 'some prize'
2 | 2012-01-28 08:00:00 | 'Company A' | 2 | 'ipad'
3 | 2012-01-28 08:00:00 | 'Company A' | 1 | 'junky phone'
4 | 2012-01-28 08:00:00 | 'Company b' | 4 | 'iphone4'
奖品类型
1 | 'Prize 1'
2 | 'Prize 2'
3 | 'Prize 3'
4 | 'Consolation Prize'
将其查询为:
SELECT
Company,
Date,
Prize_Item,
Prize_Description
FROM Prizes JOIN Prize_Types ON Prize_Types.Prize_Type = Prizes.Prize_Type
WHERE Prize_Item = 'iphone4'
实际上,您可能还希望公司和奖品规范化为自己的表,就像奖品类型一样。