how to save select result into variable in mysql [closed]

Question

Closed. This question needs details or clarity. It is not currently accepting answers.

Want to improve this question? As written, this question is lacking some of the information it needs to be answered. If the author adds details in comments, consider editing them into the question. Once there's sufficient detail to answer, vote to reopen the question.

Closed 2 years ago.

Improve this question

origin link: https://leetcode.com/problems/human-traffic-of-stadium/description/ the ideal pseudo code I want is

select id from Stadium where people >= 100 into @tmp;
select * from Stadium 
where id in tmp and (
(id + 1 in tmp and id + 2 in tmp) or
(id + 1 in tmp and id - 1 in tmp) or
(id - 1 in tmp and id - 2 in tmp) 
)
order by visit_date;

what I tried is as follow, but that is too stubborn:

select * from Stadium 
where id in (select id from Stadium where people >= 100) and (
(id + 1 in (select id from Stadium where people >= 100) and id + 2 in (select id from Stadium where people >= 100)) or
(id + 1 in (select id from Stadium where people >= 100) and id - 1 in (select id from Stadium where people >= 100)) or
(id - 1 in (select id from Stadium where people >= 100) and id - 2 in (select id from Stadium where people >= 100)) 
)
order by visit_date;

If Leetcode supported MySQL 8.0, I would suggest using a common table expression, but I recall they are still using MySQL 5 — Bill Karwin
– Bill Karwin, Commented Oct 20, 2023 at 14:47
The proposed dup target is about Oracle SQL, which ignores many MySQL based Questions, such as this one about using SET with a select statement. However the wording of the Question here is confusing to me, as it seems less about assigning a value to a variable than about creating a temp table. Voting to close for lack of clarity rather than as a duplicate. — hardmath
– hardmath, Commented Oct 22, 2023 at 2:24

diacoviello · Accepted Answer · 2023-10-20 20:03:07Z

Are you trying to return a list of results into a variable? Cause honestly, that gets complicated and, honestly, isn't worth the headache. Right now the way you wrote it, it is returning table data which cannot be added into a variable. If you want the ids in a list, you could do:

`SET @tmp = (SELECT GROUP_CONCAT(id Separator ',') where people >= 100);`

This will give you a comma separated list, like 'Stadium1,Stadium2,...' which you could use for other mysql functions like FIND_IN_SET(), etc. (more examples here)

I suggest making CTE(s) with the stadiums that meet your criteria, then JOIN to the original table and query away. Such as:

WITH cte (id, name, columns...) 
AS (Select * from Stadium where people >= 100)
/* Select statements */

CTEs make querying much much faster and don't require creating and dropping tables. you can also create multiple CTEs by just putting a comma after each one. Hope this helps!

Collectives™ on Stack Overflow

how to save select result into variable in mysql [closed]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related