order by

[yeahz]

ir šāda tabula

 

+----+------+-------+
| id | plus | minus |
+----+------+-------+
| 1  | 8    | 3     |
+----+------+-------+
| 2  | 1    | 2     |
+----+------+-------+
| 3  | 3    | 6     |
+----+------+-------+
| 4  | 1    | 1     |
+----+------+-------+
| 5  | 2    | 4     |
+----+------+-------+

 

vai ir iespējams izveidot ORDER BY tā, lai viņš rēķinātu pēc plus-minus (no plusa atņem mīnusu)? tātad lai sanāk šādi:

 

+----+------+-------+
| id | plus | minus |
+----+------+-------+
| 1  | 8    | 3     |    jo 8-3=5
+----+------+-------+
| 4  | 1    | 1     |    jo 1-1=0
+----+------+-------+
| 2  | 1    | 2     |    jo 1-2=-1
+----+------+-------+
| 5  | 2    | 4     |    jo 2-4=-2
+----+------+-------+
| 3  | 3    | 6     |    jo 3-6=-3
+----+------+-------+

 

centos šādi:

 

SELECT * FROM table ORDER BY plus-minus DESC

 

taču šādā gadījumā viņš izvilka kaut ko pilnīgi bezsakarīgu.

Edited January 25, 2011 by yeahz

[Gints Plivna]

Viss strādā šai gadījumā. Iespējams, ka datu tips tabulā nav vis skaitliskais, piemēram, INT, bet simboliskais?

mysql> create table q (id int, plus int, minus int);
Query OK, 0 rows affected (0.09 sec)

mysql> insert into q values (1, 8, 3), (2, 1,2), (3, 3, 6), (4, 1, 1), (5, 2, 4)
;
Query OK, 5 rows affected (0.05 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from q order by plus-minus desc;
+------+------+-------+
| id   | plus | minus |
+------+------+-------+
|    1 |    8 |     3 |
|    4 |    1 |     1 |
|    2 |    1 |     2 |
|    5 |    2 |     4 |
|    3 |    3 |     6 |
+------+------+-------+
5 rows in set (0.02 sec)

 

Gints Plivna

http://datubazes.wordpress.com

[yeahz]

lauki plus un minus tiek veidoti šādi:

 

LEFT JOIN (SELECT count(*) AS count_pluses, article_id FROM (SELECT article_id FROM rating WHERE rating='+') AS rating GROUP BY article_id) AS r_plus ON r_plus.article_id=a.id
LEFT JOIN (SELECT count(*) AS count_minuses, article_id FROM (SELECT article_id FROM rating WHERE rating='-') AS rating GROUP BY article_id) AS r_minus ON r_minus.article_id=a.id

 

tabulas rating struktūra:

 

+----+------------+--------+
| id | article_id | rating |
+----+------------+--------+
| 1  | 1          | +      |
| 2  | 1          | -      |
+----+------------+--------+

Edited January 25, 2011 by yeahz

[Gints Plivna]

Atlasi arī to izteiksmi pēc kuras kārto.

Man arī šādi viss notiek:

mysql> SELECT article_id, plus, minus, plus-minus
   -> FROM (
   ->   SELECT COUNT(CASE WHEN rating = '+' THEN 1 ELSE NULL END) as plus,
   ->          COUNT(CASE WHEN rating = '-' THEN 1 ELSE NULL END) as minus,
   ->          article_id
   ->   FROM ratings
   ->   GROUP BY article_id) as q
   -> ORDER BY plus-minus DESC;
+------------+------+-------+------------+
| article_id | plus | minus | plus-minus |
+------------+------+-------+------------+
|          2 |    2 |     0 |          2 |
|          1 |    3 |     2 |          1 |
|          3 |    1 |     4 |         -3 |
+------------+------+-------+------------+
3 rows in set (0.00 sec)

un versija - tas varētu būt svarīgi:
mysql> show variables LIKE 'version';
+---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+
| version       | 5.1.52-community |
+---------------+------------------+
1 row in set (0.02 sec)

 

Gints Plivna

http://datubazes.wordpress.com