当前位置: 动力学知识库 > 问答 > 编程问答 >

database - Set prority to a row when the totals are tied in mysql

问题描述:

Example fiddle

I'm trying to find out the top 2 device features a user prefers based on the devices he has owned. The table yr_phones uses tinyint to map 0 to No and 1 to Yes for each function.

From the following example schema, the two most preferred features I am trying to get are

FUNCTION TOTAL

bluetooth 4

wifi 3

Since both wifi and GPS are 3 in total, is there any way to make sure wifi always takes precedence if it's tied with other functions?

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)

Query:

 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

;

网友答案:

The answer you are looking for:

(
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

Now this not an answer but you should consider fragmenting your database.

What if you will want to add other features one day? like front camera, water resistance etc. you will have to change every query... also queries are way more difficult...

Example below is common way how to create database and also good practice:

TABLE features: feature_id, feature_name

1 Wi-Fi

2 Bluetooth

3 4G

4 GPS

...

TABLE phone_features: product_id feature_id

1 1

1 2

1 3

It's also easy to make queries and queries are way more efficient:

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

also in this way you can add as many features you want without changing the queries

网友答案:

Start with this...

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

This is how your table should have looked in the first place

分享给朋友:
您可能感兴趣的文章:
随机阅读: