WITH
RECURSIVE search_graph(id, ref_id) AS
(
SELECT id, ref_id
FROM Users
UNION ALL
SELECT o.id, o.ref_id
FROM Users o
JOIN search_graph p ON p.ref_id = o.id
)
SELECT array_agg(id || ' -> ' ) AS "path"
FROM search_graph
GROUP BY id
ORDER BY id
;
select BSproduct from
( -- Список продуктов имеющих черный цвет и малый размер
select product as BSProduct from
(
SELECT t1.id, t1.name AS product, t3.name AS color, t5.name AS size
FROM `product` AS t1
JOIN `product_color` AS t2 ON t1.id = t2.product_id
JOIN `color` AS t3 ON t2.color_id = t3.id
JOIN `product_size` AS t4 ON t1.id = t4.product_id
JOIN `size` AS t5 ON t4.size_id = t5.id
)
where color = "black" and size = "S"
) as BlackAndSmallSize,
( -- Список продуктов имеющих черный цвет и средний размер
select product as BMProduct from
(
SELECT t1.id, t1.name AS product, t3.name AS color, t5.name AS size
FROM `product` AS t1
JOIN `product_color` AS t2 ON t1.id = t2.product_id
JOIN `color` AS t3 ON t2.color_id = t3.id
JOIN `product_size` AS t4 ON t1.id = t4.product_id
JOIN `size` AS t5 ON t4.size_id = t5.id
)
where color = "black" and size = "M"
) as BlackAndMidSize,
( -- Список продуктов имеющих большой размер
select product as LProduct from
(
SELECT t1.id, t1.name AS product, t3.name AS color, t5.name AS size
FROM `product` AS t1
JOIN `product_color` AS t2 ON t1.id = t2.product_id
JOIN `color` AS t3 ON t2.color_id = t3.id
JOIN `product_size` AS t4 ON t1.id = t4.product_id
JOIN `size` AS t5 ON t4.size_id = t5.id
)
where size = "L"
) as LargeSize
where BSProduct=BMProduct and BMProduct=LProduct
(
SELECT t1.id, t1.name AS product, t3.name AS color, t5.name AS size
FROM `product` AS t1
JOIN `product_color` AS t2 ON t1.id = t2.product_id
JOIN `color` AS t3 ON t2.color_id = t3.id
JOIN `product_size` AS t4 ON t1.id = t4.product_id
JOIN `size` AS t5 ON t4.size_id = t5.id
)
CREATE TRIGGER `update_users` AFTER UPDATE ON `users`
FOR EACH ROW
BEGIN
INSERT INTO payments(status_id, user_id, description, currency_id,
amount, bonus_amount, sum)
values('2' ,new.id, 'test', 643,
old.balance, old.balance+new.balance, new.balance);
END;
select distinct pa.attribute_id , pa.text from product_attribute pa, product_category pb
where p.product_id = pa.product_id and p.category_id=8 and
pa.product_id not in (
select pa2.product_id from product_attribute pa2
where (pa2.attribute_id=9 and pa2.text=92) or (pa2.attribute_id=8 and pa2.text=55)
) and
not((pa.attribute_id=9 and pa.text=92) or (pa.attribute_id=8 and pa.text=55))
select distinct pa.attribute_id , pa.text from product_attribute pa, product_category pb, filtr_sess fs
where p.product_id = pa.product_id and p.category_id=8 and
pa.product_id not in (
select pa2.product_id from product_attribute pa2, filtr_sess s
where (pa2.attribute_id=s.id and pa2.text=s.text and s.sid=X)
) and fs.sid=X and pa.attribute_id <> fs.id and pa.text <> fs.text
select product_id as id, products.name,
group_concat(sizes.value separator ';') as sizes
from product_sizes, sizes, products
where product_id=products.id and size_id=sizes.id
group by product_id
select products.id, products.name,
group_concat(sizes.value separator ';') as sizes
from product_sizes
inner join sizes on (size_id=sizes.id)
right join products on (product_id=products.id)
group by product_id order by id;
`Phone` varchar(15) NOT NULL
`Phone` char(15) NOT NULL
или (если не хранятся в значениях нецифровые символы - скобки, например)`Phone` int NOT NULL
mysql> select email, group_concat(concat(character_name, ':',
class_name, ' ',character_level,' lvl') separator ', ') as Chars
from characters,players,classes
where player_id=players.id and class_id=classes.id group by player_id;
+--------+--------------------------------------------+
| email | Chars |
+--------+--------------------------------------------+
| a@a.ru | Merlin:wizard 70 lvl, Conan:warrior 80 lvl |
| b@b.ru | Azariel:warrior 50 lvl, Bilbo:thief 20 lvl |
+--------+--------------------------------------------+
2 rows in set (0.00 sec)