Tag Archives: SQL

postgresql-Database query problem-ERROR: column “t.domainid“ must appear in the GROUP BY clause or be used in an a

ERROR: column “t.domainid” must appear in the GROUP BY clause or be used in an aggregate function

The data table is as follows.

 select * from td_link_1h_d_20210427 limit 10;
 
    time    | linkid | domainid | ipversion |    upbps    |    dnbps    | updisbps | dndisbps  |  upmaxbps   |  dnmaxbps   |  upminbps   |  dnminbps   
------------+--------+----------+-----------+-------------+-------------+----------+-----------+-------------+-------------+-------------+-------------
 1619452800 |   4401 |    10001 |         4 |   147856388 |     5923764 |        0 |         0 |   166378265 |    12533696 |   123212832 |     2247349
 1619452800 |   5002 |    10001 |         6 |           0 |           0 |        0 |         0 |           4 |           4 |           0 |           0
 1619452800 |   5503 |    10001 |         6 |           0 |         149 |        0 |         0 |           2 |         194 |           0 |         102
 1619452800 |   8724 |    10001 |         6 |         238 |           0 |        0 |         0 |         609 |           2 |          23 |           0
 1619452800 |   1201 |    10001 |         4 |   255570975 |  4111481619 |  3035634 |  19703184 |   292787968 |  4751690795 |   209619623 |  3334699801
 1619452800 |   4700 |    10001 |         4 | 19626007220 |  8697825644 |        0 |         0 | 23504765993 |  9930189669 | 16586678202 |  7205462505
 1619452800 |  13249 |    10001 |         6 |     3066257 |      262560 |        0 |        12 |     5817708 |      339458 |      954397 |      217159
 1619452800 |   9156 |    10001 |         4 | 19773923700 |  2984265593 |        0 |         0 | 22335976464 |  3388045463 | 17124116138 |  2543622015
 1619452800 |  11741 |    10001 |         4 |  5628620627 | 20052950343 | 51741713 | 318854796 |  6958633624 | 23485369390 |  4357751762 | 15861228522
 1619452800 |   2700 |    10001 |         4 |   657502770 |  2246692965 |        0 |         0 |   845823645 |  2394743374 |   506699006 |  1880772827
(10 rows)

The following error occurred when querying the database by grouping statistics:

select linkid as "ID",domainid as "Domain",to_char(time::abstime,'YYYYMMDD') as "DATE",to_char(time::abstime,'HH24') as "Time",round(sum(case when time >= '2021-06-07 14:00:00'::abstime::int  and time < '2021-06-08 13:59:59'::abstime::int then upbps else 0 end )/1000/1000,3) as "Mbps",round(sum(case when time >= '2021-05-31 14:00:00'::abstime::int  and time < '2021-06-01 13:59:59'::abstime::int then upbps else 0 end )/1000/1000,3) as "Mbps",round(sum(case when time >= '2021-06-07 14:00:00'::abstime::int  and time < '2021-06-08 13:59:59'::abstime::int then dnbps else 0 end )/1000/1000,3) as "下行速率Mbps",round(sum(case when time >= '2021-05-31 14:00:00'::abstime::int  and time < '2021-06-01 13:59:59'::abstime::int then dnbps else 0 end )/1000/1000,3) as "下行对比速率Mbps",round(sum(case when time >= '2021-06-07 14:00:00'::abstime::int  and time < '2021-06-08 13:59:59'::abstime::int then updisbps else 0 end )/1000/1000,3) as "上行丢弃速率Mbps" from (((select time, linkid,domainid,ipversion,upbps, dnbps, updisbps from td_link_1h_d_20210531 UNION all select time, linkid,domainid,ipversion,upbps, dnbps, updisbps from td_link_1h_d_20210601) UNION all select time, linkid,domainid,ipversion,upbps, dnbps, updisbps from td_link_1h_d_20210607) UNION all select time, linkid,domainid,ipversion,upbps, dnbps, updisbps from td_link_1h_d_20210608)T where (time >= '2021-06-07 14:00:00'::abstime::int  and time < '2021-06-08 13:59:59'::abstime::int )  or  (time >= '2021-05-31 14:00:00'::abstime::int  and time < '2021-06-01 13:59:59'::abstime::int) and (ipversion in (4,6)) group by linkid,time order by linkid,time;

ERROR:  column "t.domainid" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select linkid as "ID",domainid as "Domain",to_char(time::abs...
                                   ^

After checking a lot of data, it is found that the error means that the domainid field must appear in group by or be used for aggregation function

so I modify the SQL statement, add the domainid field to group by, and then query again

 select linkid as "ID",domainid as "Doamin",to_char(time::abstime,'YYYYMMDD') as "DATE",to_char(time::abstime,'HH24') as "TIME",round(sum(case when time >= '2021-06-07 14:00:00'::abstime::int  and time < '2021-06-08 13:59:59'::abstime::int then upbps else 0 end )/1000/1000,3) as "上行速率Mbps",round(sum(case when time >= '2021-05-31 14:00:00'::abstime::int  and time < '2021-06-01 13:59:59'::abstime::int then upbps else 0 end )/1000/1000,3) as "上行对比速率Mbps",round(sum(case when time >= '2021-06-07 14:00:00'::abstime::int  and time < '2021-06-08 13:59:59'::abstime::int then dnbps else 0 end )/1000/1000,3) as "下行速率Mbps",round(sum(case when time >= '2021-05-31 14:00:00'::abstime::int  and time < '2021-06-01 13:59:59'::abstime::int then dnbps else 0 end )/1000/1000,3) as "下行对比速率Mbps",round(sum(case when time >= '2021-06-07 14:00:00'::abstime::int  and time < '2021-06-08 13:59:59'::abstime::int then updisbps else 0 end )/1000/1000,3) as "上行丢弃速率Mbps" from (((select time, linkid,domainid,ipversion,upbps, dnbps, updisbps from td_link_1h_d_20210531 UNION all select time, linkid,domainid,ipversion,upbps, dnbps, updisbps from td_link_1h_d_20210601) UNION all select time, linkid,domainid,ipversion,upbps, dnbps, updisbps from td_link_1h_d_20210607) UNION all select time, linkid,domainid,ipversion,upbps, dnbps, updisbps from td_link_1h_d_20210608)T where (time >= '2021-06-07 14:00:00'::abstime::int  and time < '2021-06-08 13:59:59'::abstime::int )  or  (time >= '2021-05-31 14:00:00'::abstime::int  and time < '2021-06-01 13:59:59'::abstime::int) and (ipversion in (4,6)) group by linkid,time,domainid order by linkid,time,domainid;

[How to Solve] Content with element type ‘mapper’ must match

Today, in the mapper. XML file, I mistakenly annotated it with /* * code description */ and then reported this error
the specific exception is as follows

Caused by: org.xml.sax.SAXParseException; lineNumber: 134; columnNumber: 10, The element type "mapper" must match "(cache-ref|cache|resultMap*|parameterMap*|sql*|insert*|update*|delete*|select*)+"。

error code 

   /** Public conditions */
    <sql id="commonIfWhere">
        <if test='params.projectId != null and params.projectId != ""'>
            and project_id = #{params.projectId}
        </if>
        <if test='params.id!= null and params.id != ""'>
            and id = #{params.id}
        </if>
    </sql>

after correction

   <!-- Public conditions -->
    <sql id="commonIfWhere">
        <if test='params.projectId != null and params.projectId != ""'>
            and project_id = #{params.projectId}
        </if>
        <if test='params.id!= null and params.id != ""'>
            and id = #{params.id}
        </if>
    </sql>

The problem was solved smoothly

[Two Methods] Ora-00904: invalid group by error identifier

I believe many students encounter the problem of invalid prompt identifier when using group by. Don’t worry, let’s summarize the common solutions to this problem:

Example: take the information of the youngest employee in each department
my SQL statement is as follows:

select name,min(age),(select deptname from dept d where d.deptid=uif.deptid)deptname from userinfo uif group by name,deptname

When you click execute, the system prompts:

ORA-00904:"deptname":Identifier is invalid

Error reason: the field after group by cannot be an alias (if you want to use an alias, you need to nest it at one level)

Solution 1:

--goup by The department name (alias: deptname ) is directly used in the deptid field of the employee information table
select name,min(age),(select deptname from dept d where d.deptid=uif.deptid)deptname from userinfo uif group by uif.name,uif.deptid

Solution 2:

--Grouping after one level of nesting
select us.name,min(us.age),us.deptname from （select name,age,(select deptname from dept d where d.deptid=uif.deptid)deptname from userinfo uif）us
group by us.name,us.deptname

How about this time?If you have any questions, please leave a message to study together~

1093 – You can’t specify target table ‘table’ for update in FROM clause

The general meaning is that you can’t select some values in the table first, and then update the table (in the same statement)

 
A temporary table can be introduced for operation

delete from push_ plans WHERE id in  
(
     select id from  
     (
         select id from push_ plans WHERE   area_ id = 0
         
     ) as t
)  
 

Caused by: java.sql.SQLException: Parameter index out of range (1 ＞ number of parameters, which is 0

1、 Error analysis

It can be seen from the error prompt that the actual parameters passed in are larger than the parameters to be set in SQL, that is, the parameters in SQL?Less than parameter or?There is no
cause at all

？ The number is surrounded by single quotation marks

For example:

sql += " and article_title like '%#{articleTitle}%'";

2、 Solutions

Remove single quotes

The above SQL is changed to:

sql += " and article_title like concat('%',#{articleTitle},'%')";

MYSQL 5.7 Error Code: 1290. The MySQL server is running with the –secure-file-priv option so it..

When exporting data with MySQL 5.7, an error was reported. The error was reported as follows:

Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

According to the wrong information, we found that secure-file-priv will specify the folder as the place where the exported files are stored, so we can find this folder first.

Solution 1:

enter the following command in the MySQL command line interface:

show variables like '%secure%';


Annotated is the correct file path, we will export files in this directory.
For SQL instructions, modify as follows:

SELECT * FROM User details WHERE gender='male'
INTO OUTFILE 'C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Uploads\\man.txt'

The file can be successfully exported to this directory.
Solution 2:
Go to the installation path C:\ProgramData\MySQL\MySQL Server 5.7, find my. Ini file and modify the default save path of Secure-file-priv.
Secure_file_prive =null — mysqld does not allow import and export
secure_file_priv=/ TMP/– limits the import and export of mysqld to only occur in the/TMP/directory
secure_file_priv= “” — does not restrict the import and export of mysqld

Mysqli::stmt Fatal error: Call to a member function bind_param() on a non-object in …

$name = “Ming”;
$sex = “male”;
$age=20;
$sql=”insert into student(name,sex,age) values (?,?,?,?) “;
$mysqli_stmt=$mysqli-> prepare($sql);
$mysqli_stmt-> bind_param(‘ssi’, $name,$sex,$age);No syntax error is indicated, but data insertion is always unsuccessful. On checking, $SQL =”insert into Student (name,sex,age) values (?,?,?,?) “; One too many?, there is no corresponding parameter.

The solution of duplicate entry ‘for key’ primary ‘when inserting data in MySQL

The problem is the key duplication encountered in inserting data table.
solution:
1. Using ignore, if there are duplicate values in the inserted record, the record row with duplicate values will be ignored, and the insertion of other rows will not be affected.

INSERT IGNORE INTO Table_name(…..) VALUES(1,1),(2,2),(3,3);

2. Use replace to delete the duplicate record row in the table before inserting when the inserted record encounters primary key or unique duplicate

REPLACE INTO Table_name() VALUES(1,1),(2,2),(3,3)

3. Using values after on duplicate key update refers to the value of the inserted record, while not using values refers to the value of the table itself. The record of the subsequent update is the ID of the duplicate primary key or unique key where.

NSERT TO Table_name() VALUES(1,1),(1,2) ON DUPLICATE KEY UPDATE NAME1=NAME1+1;

Download: https://blog.csdn.net/zhangyr_student/article/details/80119238

Mysql Script Error Code: 1136. Column count doesn’t match value count at row 1

Running mysql script during development, mysql prompts Column count doesn’t match value count at row 1 error. After sticking, it is found that the number of columns in the SQL statement is inconsistent with the number of subsequent values. For example, insert into table names (field1,field2,field3) values(‘a’,’b’).

Solve ERROR 1136 (21S01) in Mysql: Column count doesn’t match value count at row 1

The problem is that when a new row is inserted, an error is returned.
type:

mysql> insert into students values(1,"goutou",18,"male",12121);

Returns:

ERROR 1136 (21S01): Column count doesn't match value count at row 1

check statement is ok. Considering the previous operation of deleting a column, two new columns are added, and the input is:

mysql> insert into students(id,name,age,gender,clc_id) values(0,"zhutou",12,"male",12121);
Query OK, 1 row affected (0.06 sec)

 

How to quickly locate the problem in DB2-407 error reporting SQLCODE: -407, SQLSTATE: 23502

SQLCODE: -407, SQLSTATE: 23502: The exact description of this error is such that a NULL value cannot be inserted into a column defined as NOT NULL. This is a violation of the integrity constraint exception.

in development, this error is something that we often encounter. The problem is simply, “Columns that cannot be empty are empty.” For a table with a large number of fields, it is difficult to sift. How to accurately

positioning?It’s actually pretty simple.

We can learn more from the error message, which can help us pinpoint the problem. Common error messages are as follows:

com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -407, SQLSTATE: 23502, SQLERRMC: TBSPACEID=2, TABLEID=201, COLNO=3
	at com.ibm.db2.jcc.b.sf.d(sf.java:1396)
	at com.ibm.db2.jcc.c.jb.l(jb.java:356)
	at com.ibm.db2.jcc.c.jb.a(jb.java:64)
	at com.ibm.db2.jcc.c.w.a(w.java:48)
	at com.ibm.db2.jcc.c.dc.c(dc.java:312)
	at com.ibm.db2.jcc.b.tf.cb(tf.java:1723)
	at com.ibm.db2.jcc.b.tf.d(tf.java:2315)
	at com.ibm.db2.jcc.b.tf.Z(tf.java:1326)
	at com.ibm.db2.jcc.b.tf.execute(tf.java:1310)
	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
	at java.lang.Thread.run(Thread.java:662)

Message: Integrity constraint exception (  
--- The error occurred in D:\------- path omitted-------.  
--- The error occurred while applying a parameter map.  
--- Check the aaa.sql-InlineParameterMap.  
--- Check the statement (update failed).  
--- Cause: com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -407, SQLSTATE: 23502, SQLERRMC: TBSPACEID=2, TABLEID=201, COLNO=3)

observe this error message carefully: SQLCODE: -407, SQLSTATE: 23502, SQLERRMC: TBSPACEID=2, TABLEID=201, COLNO=3. You can see that the error message has been located very well

is clear. We only need to query in syscat.columns of the system table. The syscat.columns table is a system table that holds detailed information about the COLUMNS of all tables in a DB2 database. We just need the basis

view, table name, column number can be queried to find which column. COLNO=3 in the error message above is the column number for which the error was reported. As follows:

SELECT 
	* 
FROM 
	SYSCAT.COLUMNS 
WHERE 
	TABSCHEMA = 'DB' AND 
	TABNAME = 'TT_PARAM_LOG' AND 
	COLNO = '3'