在mysql中将总计设置为一行。

Question

例琴

我正试图根据用户所拥有的设备来找出用户喜欢的前2种设备功能。表yr_phones使用tinyint为每个函数映射0到No和1到Yes。

从下面的示例模式中，我想要获得的两个最喜欢的特性是

FUNCTION    TOTAL
bluetooth   4
wifi        3

既然wifi和GPS总共都是3，那么如果wifi和其他函数绑定在一起的话，有什么办法确保wifi总是优先的呢？

CREATE TABLE yr_phones
    (`product_id` int,`wifi` int,`GPS` int,`backlighting` int,`4G` int,`bluetooth` int)
;

INSERT INTO yr_phones
    (`product_id`,`wifi`,`GPS`,`backlighting`,`4G`,`bluetooth`)
VALUES
    (1,1,1,1,0,0),
    (2,1,1,0,1,0),
    (3,1,0,0,1,1),
    (4,0,1,0,0,1),
    (5,0,0,0,0,1),
    (6,0,0,1,0,1)

查询：

   SELECT 'wifi' AS function,SUM(wifi) AS total
   FROM yr_phones
   UNION ALL
   SELECT 'GPS' AS function,SUM(GPS) AS total
   FROM yr_phones
   UNION ALL
   SELECT  'backlighting' AS function,SUM(backlighting) AS total
   FROM yr_phones
   UNION ALL
   SELECT 'bluetooth' AS function, SUM(bluetooth) AS total
   FROM yr_phones
   UNION ALL
   SELECT '4G' AS function, SUM(4G) AS total
   FROM yr_phones
   ORDER BY total DESC
   LIMIT 2

    ;

Answer 1

你想要的答案是：

(
SELECT 'wifi' AS function,SUM(wifi) AS total
FROM yr_phones
UNION ALL
SELECT 'GPS' AS function,SUM(GPS) AS total
FROM yr_phones
UNION ALL
SELECT  'backlighting' AS function,SUM(backlighting) AS total
FROM yr_phones
UNION ALL
SELECT 'bluetooth' AS function, SUM(bluetooth) AS total
FROM yr_phones
UNION ALL
SELECT '4G' AS function, SUM(4G) AS total
FROM yr_phones
) tmp
ORDER BY 
tmp.total DESC, 
(tmp.`function` = 'wifi') DESC -- this is the priority thing you are looking for
LIMIT 2

现在，这不是一个答案，但您应该考虑分割您的数据库。

如果有一天你想添加其他功能，该怎么办？就像前面的摄像头，防水等，你必须改变每一个查询.而且查询要困难得多。

下面的示例是创建数据库的常用方法，也是很好的实践：

表格特性: feature_id，feature_name 1个 2蓝牙 34G 4 GPS ..。 表phone_features: product_id feature_id 1 1 1 2 1 3

使查询和查询更有效也很容易：

SELECT
    feature_name,
    COUNT(*) as total
FROM
    phone_features
LEFT JOIN
    features ON 
    features.feature_id = phone_features.feature_id
GROUP BY
    features.feature_ID
ORDER BY
    total DESC,
    feature_name = 'wifi' DESC
LIMIT 2

同样，您可以在不更改查询的情况下添加任意数量的功能。

Answer 2

从这个开始..。

SELECT product_id,'wifi' AS function,wifi total
FROM yr_phones
UNION ALL
SELECT product_id,'GPS',GPS
FROM yr_phones
UNION ALL
SELECT  product_id,'backlighting' ,backlighting
FROM yr_phones
UNION ALL
SELECT product_id,'bluetooth',bluetooth
FROM yr_phones
UNION ALL
SELECT product_id,'4G',4G
FROM yr_phones

首先，你的桌子应该是这样的。