Asked Aug 16th, 2021 3:32 a.m. 161 0 1
• 161 0 1
-1

# Ai giúp mình chuyển câu sql này qua laravel với

• 161 0 1
``````SELECT
COUNT(b.book_id) AS number_book,
b.language,
SUM(b.single) AS single_number,
SUM(b.couple) AS couple_number
FROM
(SELECT
b.book_id,
b.language,
ROUND(SUM(CASE WHEN plt.depend_flag = 1 THEN .5 ELSE 0 END)) AS couple,
SUM(CASE WHEN plt.depend_flag = 0 THEN 1 ELSE 0 END) AS single
FROM book AS b
INNER JOIN plan AS pl ON b.book_id = pl.book_id
LEFT JOIN plan_type AS plt ON pl.plan_type_id = plt.plan_type_id
INNER JOIN customer c ON pl.plan_id = c.plan_id
WHERE
b.status = 1
AND b.book_type IN (1 , 3)
AND b.create_time >= '2020-08-04 00:00:00'
AND b.create_time <= '2020-08-04 23:59:59'
GROUP BY b.book_id) b
GROUP BY b.language
``````

Answered Aug 16th, 2021 3:38 a.m.
+6
``````DB::raw("SELECT COUNT(b.book_id) AS number_book, b.language, SUM(b.single) AS single_number, SUM(b.couple) AS couple_number FROM (SELECT b.book_id, b.language, ROUND(SUM(CASE WHEN plt.depend_flag = 1 THEN .5 ELSE 0 END)) AS couple, SUM(CASE WHEN plt.depend_flag = 0 THEN 1 ELSE 0 END) AS single FROM book AS b INNER JOIN plan AS pl ON b.book_id = pl.book_id LEFT JOIN plan_type AS plt ON pl.plan_type_id = plt.plan_type_id INNER JOIN customer c ON pl.plan_id = c.plan_id WHERE b.status = 1 AND b.book_type IN (1 , 3) AND b.create_time >= '2020-08-04 00:00:00' AND b.create_time <= '2020-08-04 23:59:59' GROUP BY b.book_id) b GROUP BY b.language");
``````
LongThanh @LongThanh.it
Aug 23rd, 2021 10:58 a.m.

Haha, ngắn gọn súc tích

Let's register a Viblo Account to get more interesting posts.