SQL查询在选择多列时返回值，但只选择一列时没有数据 [英] SQL query returns values when selecting multiple columns, but no data when selecting only one column

问题描述

仅选择一列时无法返回数据.此列是一个整数格式，列名为日期".我正在使用 SQLite3.

I can't get data to return when selecting only one column. This column is an integer format with a column name of "date". I'm using SQLite3.

sqlite> .once table.txt
sqlite> select "date",* from messages limit 3;
sqlite> .once justColumn.txt
sqlite> select "date" from messages limit 3;

输出表格.txt

date|id|folderID|messageKey|conversationID|date|headerMessageID|deleted|jsonAttributes|notability
1556338536000000|32|2|13330|1|1556338536000000|[e-mail]|0|{"43":1,"44":[2],"45":[],"46":[],"50":false,"51":[],"52":[1,2],"53":[2],"55":[[2,1]],"57":[],"58":false,"59":false,"60":false,"61":false}|1
1556339727000000|33|8|2|2|1556339727000000|[e-mail]|0|{"43":1,"44":[2],"45":[],"46":[],"50":false,"51":[],"52":[1,2],"53":[2],"57":[],"58":false,"59":true,"60":false,"61":false}|0
1557332781000000|34|2|13430|3|1557332781000000|1605547663.1557332777559.JavaMail.cfservice@SL132APP2|0|{"43":3,"44":[2],"45":[],"46":[],"50":false,"51":[],"52":[3,2],"53":[2],"55":[[2,3]],"57":[],"58":false,"59":false,"60":false,"61":false}|1

justColumn.txt

justColumn.txt

date

我希望两个查询在第一列中返回相同的数据，但事实并非如此.什么是?

I'd expect both queries to return the same data in the first column, but that's not what's happening. What is?

推荐答案

来自文档:

如果返回多行的 SELECT 语句没有 ORDER BY 子句，则返回行的顺序未定义.

If a SELECT statement that returns more than one row does not have an ORDER BY clause, the order in which the rows are returned is undefined.

我可以用这张表重现您看到的内容:

I'm able to reproduce what you're seeing with this table:

CREATE TABLE test(id INTEGER PRIMARY KEY, date INTEGER, foo, bar);
INSERT INTO test(date, foo, bar) VALUES (25, 'a', 'b'), (50, 'c', 'd'), (75, 'e', 'f');
INSERT INTO test(foo,bar) VALUES ('g', 'h'), ('i', 'j'), ('k', 'l'); -- null dates
CREATE INDEX test_idx_date ON test(date);

第一次查询:

sqlite> SELECT * FROM test LIMIT 3;
id          date        foo         bar       
----------  ----------  ----------  ----------
1           25          a           b         
2           50          c           d         
3           75          e           f         
sqlite> EXPLAIN QUERY PLAN SELECT * FROM test LIMIT 3;
QUERY PLAN
`--SCAN TABLE test

第二次查询:

sqlite> .null (null)
sqlite> SELECT date FROM test LIMIT 3;
date      
----------
(null)
(null)
(null)
sqlite> EXPLAIN QUERY PLAN SELECT date FROM test LIMIT 3;
QUERY PLAN
`--SCAN TABLE test USING COVERING INDEX test_idx_date

第二个从具有请求列的索引而不是完整表中提取结果，并且该索引中的行以与表中不同的顺序返回，因此结果不同.我想您的表的情况类似.

The second one pulls results from an index with the requested column instead of the full table, and the rows in that index are returned in a different order than the ones in the table, thus different results. I imagine the situation is similar for your table.

这篇关于SQL查询在选择多列时返回值，但只选择一列时没有数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！