On 2015-07-31 03:40 PM, Gabor Grothendieck wrote:
> On Fri, Jul 31, 2015 at 8:40 AM, Simon Slavin <slavins at bigfraud.org> wrote:
>
>> I am noting an overlap with the things SQLite users have been using
>> sub-SELECTs for.
>>
>>
> Here is a self contained example that can be fed into the sqlite3 command
> line tool. It uses WITH to factor out the subquery; however, the annoying
> part about it is that even though we have factored out the subquery we
> still have to include a simplified version of the subquery, viz.
> (select avgSalary from A)
> It would have been nice if we could just replace (select avgSalary from A)
> with (A.avgSalary) or even (A) but those substitutions do not work:
Well, you can, sort-of, if you use the CTE column naming. Consider this
simplified version of your Query (also using the CTE to do the actual
aggregate work once only):
create table Emp (emp text, salary real, dept text);
insert into Emp values
('a', 1, 'A'),
('b', 2, 'A'),
('c', 3, 'A'),
('d', 1, 'B'),
('e', 2, 'B'),
('f', 3, 'B');
WITH A(dept,avgSalary) as (
SELECT Emp.dept, AVG(salary) FROM Emp GROUP BY Emp.dept
)
SELECT Emp.*
FROM Emp, A
WHERE Emp.dept = A.dept AND Emp.salary > A.avgSalary;
-- emp | salary | dept
-- ----- | ------ | -----
-- c | 3.0 | A
-- f | 3.0 | B
> -- based on query at: https://en.wikipedia.org/wiki/Correlated_subquery
> create table Emp (emp text, salary real, dept text);
> insert into Emp values ('a', 1, 'A');
> insert into Emp values ('b', 2, 'A');
> insert into Emp values ('c', 3, 'A');
> insert into Emp values ('d', 1, 'B');
> insert into Emp values ('e', 2, 'B');
> insert into Emp values ('f', 3, 'B');
> WITH A as
> (SELECT AVG(salary) AS avgSalary FROM Emp WHERE dept = e1.dept)
> SELECT *
> FROM Emp AS e1
> WHERE salary > (select avgSalary from A);
>
> Here is a sample run assuming the above has been placed into a file named
> test.sqlite.
>
> C:\> sqlite3 < test.sqlite
> c|3.0|A
> f|3.0|B
>
> (I am using sqlite 3.8.3 on Windows 8.1.)
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
