Asked Aug 16th, 3:32 AM 152 0 1
  • 152 0 1
-1

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

Share
  • 152 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

1 ANSWERS


Answered Aug 16th, 3:38 AM
+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");
Share
Avatar LongThanh @LongThanh.it
Aug 23rd, 10:58 AM

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

+1
| Reply
Share
Viblo
Let's register a Viblo Account to get more interesting posts.