Geek asks and answers

Order for the invoice amount of two fields

Ok, I have an interesting problem here. I have two tables, tips and voices. The tips are as follows:

tip_id, challenge_id, user_id, ip_address, tip_date, tip_text

The voices are as follows:

tip_id, ip_address, vote_date, vote

Users can post call tips, and then other users can vote at the tip of their finger up or down. Voting field in votes - bool, 0 - thumb, 1 - thumb up. A task can have any number of tips.

I tormented my brain, and I just can't get it to work. I need a query that can get the number of votes up and down for a specific hint, the difference of these two numbers (so if there are 2 votes and 1 vote down, the difference will be 1) as a rating, and also tell me the data from the hint table, then first Sort it with the highest rating. Suppose we have two hints: a and b:

a: 3 up, 2 down (difference: 1) b: 4 up, 1 down (difference: 3)

In this case, b will be the first, since it has a higher rating than a, a greater difference between the number of votes up and down.

So, for example, let's say that I have a hint with id 1 that has 4 votes and 2 votes, which means a difference of 2, and this tip is related to calling id 5, my return might look like this

up | down | rating | tip_id | tip_date | user_id | tip_text
------------------------------------------------------------
3  | 2    | 1      | 1      | 12345678 | 1       | "this is a tip"

, , , , . , id 5 2 , :

up | down | rating | tip_id | tip_date | user_id | tip_text
------------------------------------------------------------
3  | 2    | 1      | 1      | 12345678 | 1       | "this is a tip"
4  | 1    | 3      | 1      | 87654321 | 2       | "this is also a tip"

. , , , . , , 7 3, .

, , , , PHP. , X , X - .

, , . , , .

: , . , , :

SELECT
  COUNT(CASE WHEN V.vote = 1 THEN 'up' ELSE NULL END) AS up,
  COUNT(CASE WHEN V.vote = 0 THEN 'down' ELSE NULL END) AS down,
  (COUNT(CASE WHEN V.vote = 1 THEN 'up' ELSE NULL END)-COUNT(CASE WHEN V.vote = 0 THEN 'down' ELSE NULL END)) AS rating,
  T.*
  FROM votes V, tips T
  WHERE T.challenge_id = 10
  GROUP BY V.tip_id
  ORDER BY rating DESC

, , , ( , ). , , , , . , , 3 1 , 2, , 6 2 4. , PHP, , . - , , ? , , LEFT JOIN, , , .

+3
2

, , ianhales , , . wuery , - INNER JOIN , . , :

SELECT
  COUNT(CASE WHEN V.vote = 1 THEN 'up' ELSE NULL END) AS up,
  COUNT(CASE WHEN V.vote = 0 THEN 'down' ELSE NULL END) AS down,
  (COUNT(CASE WHEN V.vote = 1 THEN 'up' ELSE NULL END)-COUNT(CASE WHEN V.vote = 0 THEN 'down' ELSE NULL END)) AS rating,
  T.*
  FROM tips T
  LEFT JOIN votes V
  ON T.tip_id = V.tip_id
  WHERE T.challenge_id = 10
  GROUP BY T.tip_id
  ORDER BY rating DESC

, , , .

, , . .

-1

- :

SELECT v.up, v.down, (v.up - v.down) AS rating, t.tip_id, t.tip_date, t.user_id, t.tip_text
FROM tips AS t
INNER JOIN votes AS v
ON t.tip_id = v.tip_id
WHERE challenge_id=1
ORDER BY rating DESC;

WHERE challenge_id PHP, mysql. , , , , INNER JOIN, , "" "" .

EDIT:

(, , , !):

 SELECT
  COUNT(CASE WHEN V.vote = 1 THEN 'up' ELSE NULL END) AS up,
  COUNT(CASE WHEN V.vote = 0 THEN 'down' ELSE NULL END) AS down,
  (COUNT(CASE WHEN V.vote = 1 THEN 'up' ELSE NULL END)-COUNT(CASE WHEN V.vote = 0 THEN 'down' ELSE NULL END)) AS rating,
  T.*
  FROM votes V
  INNER JOIN tips AS T
  ON T.tip_id = V.tip_id
  WHERE T.challenge_id = 1
  GROUP BY V.tip_id
  ORDER BY rating DESC;

SQLite3 ( ) , , . :

sqlite> select * from votes
   ...> ;
tip_id      vote      
----------  ----------
2           1         
2           1         
2           1         
2           0         
2           0         
3           0         
3           0         
3           0         
3           1         
4           0         
4           0         
4           1         
4           1         
4           1         
4           1

:

up          down        rating      tip_id      challenge_id  user_id   
----------  ----------  ----------  ----------  ------------  ----------
4           2           2           4           1             4         
3           2           1           2           1             2         
1           3           -2          3           1             3
+2

More articles:


All Articles